Exceljet

Quick, clean, and to the point

Count cells equal to one of many things

Excel formula: Count cells equal to one of many things
Generic formula 
=SUMPRODUCT(COUNTIF(rng,things))
Summary 

To count the number of cells equal to one of many values, you can use the COUNTIF function inside of the SUMPRODUCT function. In the generic form of the formula above, rng represents a range of cells, and things represents the values to count. In the example shown, cell G5 contains this formula:

=SUMPRODUCT(COUNTIF(B5:B10,things))

where things is the named range E5:E7.

Note: COUNTIF is not case-sensitive.

Explanation 

In this example, the goal is to count the values in column B listed in the range E5:E7. One way to do this is to give the COUNTIF function all three values in the named range things (E5:E7) as criteria, then use the SUMPRODUCT function to get a total. The formula in G4 is:

=SUMPRODUCT(COUNTIF(B5:B15,things))

The COUNTIF function counts the number of cells in a range that meet criteria. When you give COUNTIF a range of cells as the criteria, it returns an array of numbers as the result, where each number represents the count of one thing in the criteria range. In this case, the named range things (D5:D7) contains 3 values, so COUNTIF returns 3 results in an array as shown below:

=COUNTIF(B5:B15,things)
=COUNTIF(B5:B15,{"apples";"pears";"kiwis"})
={2;3;1} // result from COUNTIF

Since "apple" appears twice, "pears" appears three times, and "kiwis" appears once, the array contains the numbers 2, 3, and 1. This array is returned directly to the SUMPRODUCT function:

=SUMPRODUCT({2;3;1})

With a single array to process, SUMPRODUCT all items in the array and returns a final result, 6.

With an array constant

With a limited number of values, you can use an array constant in your formula like this:

=SUMPRODUCT(COUNTIF(B5:B15,{"apples","pears","kiwis"}))

ISNUMBER and MATCH

The above formula works fine, but has some limitations due to the nature of COUNTIF. As an alternative, you can use the formula below, which uses the ISNUMBER function with the MATCH function to achieve the same result:

=SUMPRODUCT(--ISNUMBER(MATCH(B5:B15,things,0)))

This is a more flexible formula in cases where logical conditions become more complex. It's also useful when you need to extract a value from a range in the data to use in a condition.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.