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:

- COUNT the problems
- IF the region is East

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.

- With SUMIF, you can check one column for criteria, and sum items in
**any numeric column** - With COUNTIF, you can check one column for criteria, and count the items in
**the same column**

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

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.

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:

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

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:

**=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: March 3, 2021 8:38 PM

Contextures RSS Feed