Count cells equal to one of many things

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

Related Functions

Sponsored Link

 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. 

 

Sponsored Link

0 votes. 0 / 5