Count Items Based on Another Column
How can you count items in one column, based on a criterion in a different column? We’ve shipped orders to the East region, and want to know how many orders had problems (a problem note is entered in column D). COUNTIF sounds like the right function to use, but it doesn’t work for this problem.
Easy to Sum Based on Criteria
If we want to get a total quantity for the East region orders, it’s easy to do with the SUMIF function. We can check region names in column B, then sum the amounts in column C.
Unfortunately, the COUNTIF function doesn’t work the same way. (I don’t know why!) The COUNTIF function only has 2 arguments — you select a range to check, and the criterion to look for.
So, the best that we can do with COUNTIF is to get a count of East region orders.
More Criteria With COUNTIFS
Fortunately, there is a COUNTIFS function, and we can use it to get a count based on multiple criteria. We’ll check column B for “East” region entries, and check column D for cells that are not empty.
The criterion “<>” is the “not equal to” operator. Used alone it means “not equal to ‘no text'”, so it will count cells that are not empty.
Get the Problem Count
With this COUNTIFS formula, the result of the Problems count for the East region is 2. Rows 2 and 9 have orders for the East region, and an entry in the Problems column.
Blank <> Empty
In this example, the notes were typed in the Problem column, and the remaining cells were empty. Our formula only counts the cells that are not empty.
However, if column D contained formulas, and some cells had a result of “” (an empty string), those cells would be also counted as “not empty”, just like the cells that contain text, even though they look blank.
Be sure that your blank cells are really empty, if you’re going to use this formula. Otherwise, you could use a SUMPRODUCT formula, like this one:
NOTE: Those are two minus signs before each section of the SUMPRODUCT formula, not long dashes.
Download the Sample File
To see more ways to count in Excel, you can visit the Excel Count Function page on my Contextures website, and download the sample file.
The zipped file is in xlsx format, and does not contain macros.