Count with Column Criteria

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".

Count With Criterion

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 uses criteria -- COUNT the problems IF the region is East.

A manual check shows 2 orders that meet our criteria.

Calculate with Criteria

Excel has two functions that calculate with criteria, and those functions seem similar - COUNTIF and SUMIF. The functions have an important difference though.

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:

1. the range where you want to check for a specific item
2. the criteria to use in that range
3. the range with the values to SUM

To get the total quantity for a specific region (typed in cell F2), enter this formula, in cell F5:

=SUMIF(B2:B11,F2,C2:C11)

The formula result is 471, and its 3 arguments are:

1. check rangeB2:B11
2. look for our criteria -- the region name in cell F2
3. return a SUM of quantities in range C2:C11.

COUNTIF Function

Unfortunately, the COUNTIF function doesn’t work the same way as SUMIF. The COUNTIF function only has 2 arguments:

1. a range to check for the specific criteria
2. the criterion to look for.

So, the best that we can do with COUNTIF is to get a count of East region orders.

=COUNTIF(B2:B11,F2)

We can't check for East region in one column, and then count items in a different column.

More Criteria With COUNTIFS

Instead of using COUNTIF, we can use the COUNTIFS function (in Excel 2007 and later). With COUNTIFS we can enter multiple ranges to check, and the criteria to use in those ranges.

To get the count of problem orders in the East, enter this formula in cell F5:

=COUNTIFS(B2:B11,F2,D2:D11,”<>”)

The formula result is 2, and it uses two sets of arguments:

1. Criteria set 1
   • check the Region names in criteria range 1 -- B2:B11
   • look for our Region criteria -- the region name in cell F2
2. Criteria set 2
   • check the Problem notes in criteria range 2 -- D2:D11
   • look for our Problem criteria -- cells that are not empty -- "<>"

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.

Blank or Empty String

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:

=SUMPRODUCT(--(B2:B11=F2),--(D2:D11<>""))

NOTE: Those are two minus signs before each section of the SUMPRODUCT formula, not long dashes.

Download the COUNT Sample Files

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.

More Function Tutorials

More Count Functions

Count Cells With Specific Text

SUM / SUMIF

Subtotal Feature