How to use Excel's COUNTIF and COUNTIFS Functions

Last updated on 2024-05-02.

The COUNTIF Function

Excel's COUNTIF function has two arguments, separated by a comma:

COUNTIF(from_which_cell_range_to_count, what_to_count_for)

Consider this spreadsheet in which we want to count the number of orders of value over 1,500.

We could use this formula:

=COUNTIF(C2:C20,">1500")

Or, if we use the range name nmValue for the cell range C2:C20:

=COUNTIF(nmValue,">1500")

What we couldn't count with a COUNTIF is the number of Orders for February 2021 of value over 1,500, because this involves two criteria.

COUNTIFS accepts multiple criteria. Below, we'll use it to count high-value Orders for February.

All versions of Excel possess the COUNTIF function. With Excel 2007, COUNTIFS was introduced.

Because COUNTIFS offers greater flexibility than COUNTIF, I always use COUNTIFS. So, in this article I concentrate on COUNTIFS.

The COUNTIFS Function

To count the number of orders ofvalue greater than 1,500 in the spreadsheet above, our COUNTIFS formula is this:

=COUNTIFS(nmValue,">1500")

This is the same as COUNTIF when applying one criterion.

Usefully, COUNTIFS can count according to more than one criterion.

COUNTIFS criteria beyond its first one are optional.

COUNTIFS(first_criterion_range, first_citerion_value, second_criterion_range, second_citerion_value, and so on …)

When you're applying more than one criteria, the dimesions of all criteria must be the same. That is:

• Each criterion range must have the same number of rows as the first criterion. This is the rule applying to the examples in this article
• Each criterion range must have the same number of columns as the first criterion.

These are examples of COUNTIFS counts:

• Mid value orders, e.g. those between 500 and 1,000
• High value orders for the western region. (We note that an Order No's prefix, e.g. “W”, indentifies the region)
• High value orders for both the western and northern regions
• Orders for February
• Orders for January and March combined
• Southern region's orders for Februrary.

I cover these in the sections below using two additional range names:

• nmOrderNo for cell range A2:A20
• nmDate for cell range B2:B20.

The Logic of COUNTIFS

If the COUNTIFS function in your formula has two or more criteria, then jointly they form a logical AND condition. Hence, when you asking to “Count the number of orders of value between 500 and 1,500”, your formula is this:

If you're asking for a count of values, each of which is “less than 500 or greater than 1,500”, then you'll need to take the sum of two COUNTIFS.

The summation of two COUNTIFS expressions satisfies a logical OR condition:

=COUNTIFS(nmValue,"<500")+COUNTIFS(nmValue,">1500")

Alternatively:

=SUM(COUNTIFS(nmValue,"<500"),COUNTIFS(nmValue,">1500"))

Setting Criteria Values

Unless it's a number, the value you provide as a criterion must be in the form of text, as in examples above.

Hence, in COUNTIFS(nmValue,"<500") the criterion value <500 is enclosed in speech marks to render it as text. If you attempt to omit the speech marks, then the formula won't work.

When your criterion value is a number, COUNTIFS accept it as a number or as text, whichever you choose. So, both COUNTIFS(nmValue,"500") and COUNTIFS(nmValue,500) work just fine.

In this example, we let the user set the upper and lower limits — in Cells F18 and F19:

The ampersand, &, concatenates the elements of each criteria value into a text string as required by the COUNTIFS's criteria values.

Wild Cards in Criteria Values

There are two wildcards you may incorporate into criteria values:

? The question marks stands as any single character

* The asterick symbol stands for any seqence of characters.

Hence ?p?? is a string of four characters, the second of which is P or p — COUNTIFS criteria are not case sensitive.

W* is a string of any length beginning with W or w.

??B* is a string of any length, the third character of which is B or b.

The formula in the Cell 20, above, illustrates that a wildcard may be included in a concatenation producing a criterion value.

COUNTIFS and Date Criteria

I use the ISO 8601 yyyy-mm-dd format on my spreadsheets hoping to avoid the confusion over dates caused by applied formats:

Confusion commonly arises between spreadsheets with dates formated as dd/mm/yyyy and mm/dd/yyyy.

Bearing in mind that Excel stores dates as numbers, flipping the number format in the spreadsheet above to General is one way to check what Excel is making of the formula:

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —