How to count items in one worksheet column, based on values in a different column. For example, count all orders with a note in the Problem column, but only if the Region column contains "East".
In this example, there is a list of orders, and we want to count the problems, for orders in the East region. To do that, we'll need a function that lets use use criteria:
A visual check shows there are 2 orders that meet our criteria.
This short video shows how to count in Excel, based on multiple criteria. There are written steps below the video.
Excel has two functions that calculate results based on a single criterion:
Those functions seem similar, but they have an important difference.
First, here's how the SUMIF function works -- we'll use it to get SUM of the quantity, IF the region is East.
In the SUMIF function, there are 3 arguments:
To get the total quantity for a specific region (typed in cell F2), enter this formula, in cell F5:
The formula result is 471, and its 3 arguments are:
The COUNTIF function only has 2 arguments:
So, the best that we can do with COUNTIF is to get a count of East region orders.
We can't check for East region in one column, and then count items in a different column.
This short video shows an example of using the COUNTIF function, to count based on a single criterion.
To count based on multiple criteria, use the COUNTIFS function. With COUNTIFS you can enter multiple ranges to check, and the criteria to look for in those ranges.
To get the count of problem orders in the East, enter this formula in cell F5:
The formula result is 2, and it uses two sets of arguments:
NOTE: 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.
That formula result matches the manual count that we did earlier.
This short video shows another COUNTIFS example, counting numbers in a range.
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.
To see the examples from this tutorial, download the Count Items With Criteria sample file.
The zipped file is in xlsx format, and does not contain any macros.
Last updated: March 3, 2021 8:38 PM
Contextures RSS Feed