How to Use COUNTIF With Multiple Criteria in Excel - Excelchat (2024)

You use the COUNTIF function in Excel when you want to count cells based on a single criterion. If you want to count based on multiple criteria, you will use the COUNTIFS function. Here is how to make the best use of this function.

Using COUNTIF with multiple criteria

Syntax for a single criteria is :

=COUNTIF(range, criteria)

The best function you can use for situations when you need to count cells based on multiple criteria is theCOUNTIFS function. The “s” on the end of Countif makes it plural, and that means that there are multiple criteria.

You can use up to XX criteria_range and criteria pairs in a single COUNTIFS function, each criterion ranges one MUST be the same shape.

You should note that the COUNTIFS function takes all of the criteria_range/criteria pairs and counts only those instances where all of the pairs are true.

COUNTIFS formula with the same criteria

If you want to count items with identical criteria, you still need to supply eachcriteria_range / criteriapair individually.

For example, here is the right formula to count items that have 0 both in column B and column C:

=COUNTIFS($B$2:$B$7,"=0", $C$2:$C$7,"=0")

This COUNTIFS formula returns 1 because only“Manuel”have“0”value in both columns.

If you use simple formula with a singlecriteria_rangelike COUNTIFS(B2:C7,”=0″) would show a different result – the total count of cells in the range B2:C7 containing a zero (which is 4 in this example).

COUNTIFS with two criteria in different criteria ranges

When you understand how to use COUNTIFS with one critera_range and criteria, then it becomes easy to extend it to two or more criteria_range/criteria combinations.

For example, let’s count the number of times the combination of Start and Finish appear in our spreadsheet :

=COUNTIFS(B3:B14,"Start",C3:C14,"Finish")

As you can see, this formula has returned the correct number of rows where both criteria are matched in their respective columns.

It’s worth repeating one of the rules of COUNTIFS here – the two criteria ranges must be the same shape and size or your formula won’t work, and will return a #VALUE error. Here’s an example where the second criteria range is smaller than the first:

=COUNTIFS(B2:B13,"Start",C2:C12,"Finish")

COUNTIFS with two criteria in the same criteria range

Another way to use COUNTIFS is to use the same criteria with more than one criteria_range. For example, suppose we want to count the number of times either Start or Progress appears in the Product column. You can try this formula using COUNTIFS:

=COUNTIFS(B2:B13,"Start",B2:B13,"In Progress")

Unfortunately, this formula will return a value of 0, as shown here:

Common errors with COUNTIFS

This is a common mistake that even experienced Excel users make when using COUNTIFS (and its counterpart, SUMIFS which you can learn about here).

The problem is that COUNTIFS looks in our criteria_range, B2:B13 and tries to count cells that containbothStartandProgress. Remember that we wanted to count cells that containeitherStartorProgress. Of course, there are no cells in our range that contain both Start and Progress, so the result is 0.

Ironically, the best way to find the answer in this scenario is so simple that it is often overlooked – just use two COUNTIF functions in your formula, rather than once COUNTIFS function, like this:

=COUNTIFS(B2:B13,"Start")+COUNTIF(B2:B13,"Progress")

In other words, we simply count all the rows that contain Start, and add those to the tally of rows that contain Progress.

Still need some help with Excel formatting or have other questions about Excel? Connect with a liveExcel experthere for some 1 on 1 help. Your first session is always free.