Count cells equal to one of many things

An excel formula to count cells equal to one of many things

Related Functions

 Count cells equal to one of many things
=SUMPRODUCT(COUNTIF(rng,things))

If you need to count the number of cells that contain values not equal to a particular value, you can use the COUNTIF function inside of SUMPRODUCT. In the generic form of the formula (above) rng represents a range of cells, and things represents the values to count. 

In the example, cell G5 contains this formula:

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

How the formula works:

COUNTIF counts the number of cells in the range that meet criteria you supply. When you give COUNTIF a range of cells as the criteria, it returns an array of numbers a result. Each item in this array represents the count of one thing in the range. Note that the criteria tests for equality, but is not case-sensitive.

Finally, the SUMPRODUCT function simply sums the items in the array and returns the total. 

 

0 votes. 0 / 5