Contextures

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 Criteria

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.

problem orders in East region

This short video shows how to count in Excel, based on multiple criteria. There are written steps below the video.

Calculate with Criteria

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

SUMIF Example

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.

SUMIF formula

COUNTIF Function

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.

COUNTIF formula

This short video shows an example of using the COUNTIF function, to count based on a single criterion.

More Criteria With COUNTIFS

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:

  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.

COUNTIFS formula

That formula result matches the manual count that we did earlier.

2 problem orders in East region

Another COUNTIFS Example

This short video shows another COUNTIFS example, counting numbers in a range.

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

Last updated: March 3, 2021 8:38 PM
Contextures RSS Feed