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

A manual check shows 2 orders that meet our 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:

- the
**range**where you want to check for a specific item - the
**criteria**to use in that range - 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:

- check
**range****B2:B11** - look for our
**criteria**-- the region name in cell**F2** - return a
**SUM**of quantities in range**C2:C11**.

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

- a range to check for the specific criteria
- 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.

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:

- Criteria set 1
- check the Region names in
**criteria range 1**-- B2:B11 - look for our Region
**criteria**-- the region name in cell**F2**

- check the Region names in
- Criteria set 2
- check the Problem notes in
**criteria range 2**-- D2:D11 - look for our Problem
**criteria**-- cells that are not empty -- "<>"

- check the Problem notes in

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

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.

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: November 13, 2018 11:52 AM

Contextures RSS Feed