Your Free Essential Guide on How To Use COUNTIFS Function In Excel

The COUNTIFS function in Excel with multiple criteria gives a lot of elbow room to pinpoint details important for analysis and financial modeling by using additional key metrics or criteria.

In this essential guide, we will go over the basics of COUNTIFS function in Excel, understand different parts of the COUNTIFS formula in Excel using examples for you to follow.

COUNTIFS Function In Excel Stripped Down To The Basics

COUNTIFS function in Excel is a built-in function under the Statistical Function category. It applies a set of criteria to cells covering multiple ranges and counts the number of cells that match all the criteria. The result is always a numerical value.

The COUNTIFS formula in Excel

Where:

  • Criteria_Range1. The range of cells that will be tested against Criteria 1.
  • Criteria1. The defined condition each of the cells in a Range1 is tested against.
  • Criteria_Range2. The second (optional) range that will be tested against Criteria2.
  • Criteria2. The second (optional) criteria to use on Range2.

The COUNTIFS function in Excel with multiple criteria allows each criterion to be expressed as any of the following:

  1. Numbers, Dates, and Time (10, 15/01/2018)
  2. Text (“Sales”, “Cost”, etc.)
  3. Cell Reference (A1, B1, C1)
  4. Logical Operators <,>,<>,= (“>10”, “>=20”)
  5. Wildcard characters (*,?). A question mark (?) matches any single character and an asterisk (*) matches any sequence of characters.

How does COUNTIFS function in Excel with multiple criteria work?

Each of the range’s criteria is applied to one cell at a time. If the first cell meets its respective criteria, the count goes up by 1. If the second cell meets its respective criteria, the count goes up by 1 again, and so on and so forth until all of the cells are tested against their respective criteria.

Another way to look at it is COUNTIFS function in Excel with multiple criteria takes all of the Criteria_Range1, Criteria1 pairs and counts only those occurrences where all of the pairs are true or matches.  COUNTIFS function in Excel allows up to 127 Criteria_Range1, Criteria1 pairs. This makes COUNTIFS function in Excel a robust and reliable tool to filter your data according to your specifications.

What are the key differences between COUNTIFS function in Excel and COUNTIF function?

COUNTIFS function in Excel

– Used for counting cells with multiple criteria in the same or different ranges
– Allows both contiguous and non-contiguous ranges

COUNTIF function in Excel

– Used for counting cells with a single criterion in one range
– Non-contiguous (Not adjoining) ranges are not allowed

Also, don’t miss learning with examples when using the COUNTIF function in Excel in this article: How to Use COUNTIF Function in Excel.

Learn the 5 easy ways to COUNT in Excel by reading this article: How to Use Count Functions in Excel.

Dissect the COUNTIFS Function In Excel With Multiple Criteria

By default, the COUNTIFS function in Excel is designed to match ALL the multiple criteria before cells are be counted. The COUNTIFS function in Excel follows the ‘AND’ logic.

Here are examples of COUNTIFS function in Excel with multiple criteria, we will use a hypothetical bicycle store’s Sales List below.

COUNTIFS Function in Excel with Multiple Text Criteria

If you want to count the number of cells containing Mountain Bikes AND under Complete Status, you can use the COUNTIFS formula in Excel =COUNTIFS(B3:B18, “mountain bike”,D3:D18,”complete”) which will give you a total count of 1.

You can breakdown the COUNTIFS formula in Excel as follows:

  • Range1. Cell range C3:C18 are the cells that will be tested against Criteria 1.
  • Criteria1. “mountain bike”  is the defined condition each of the cells in a Range1 is tested against.
  • Range2. Cell range E3:E18 is the second range of cells that will be tested against Criteria2.
  • Criteria2. “complete” the second criteria to use on Range2.

Notice that the range of cells is different C3:C18 and E3:E18. This is one of the key advantages of COUNTIFS function in Excel over the COUNTIF function. The same or different ranges can be used in the COUNTIFS formula in Excel.

Another similar example is counting the number of cells containing Mountain Bikes AND under Pending Status. The COUNTIFS formula in Excel =COUNTIFS(C3:C18,”mountain bike”,E3:E18,”pending”) results in a total count of 3.

As you notice, the COUNTIFS formula in Excel is not case sensitive. So, typing “mountain bike” or “Mountain Bike” will still give a total count of 3.

COUNTIFS Function in Excel with Wildcard Characters

In the real world, details in your data are not entirely unique. Data can have different versions and may contain similar characters. You can use the following wild characters to filter your criteria.

  • Asterisk (*) – matches any one or more characters. This is used when counting cells containing a specific word or a character(s).
  • Question mark (?) – matches any single character. This is used when counting cells beginning and/or ending with certain characters.

To illustrate, assume the Mountain Bike has a second version, simply called Mountain Bike v2. If you want to count the number of cells containing Mountain Bikes regardless of any version, use the Asterisk (*) in your COUNTIFS formula in Excel.

Continuing with our prior example, so to count the number of cells containing Mountain Bikes regardless of any version AND under Pending Status, the COUNTIFS formula in Excel =COUNTIFS(C3:C18,”*mountain bike*”,E3:E18,”pending”) will give us a total count of 3.

COUNTIFS Function in Excel with Multiple Number Criteria

COUNTIFS function in Excel is very useful in counting cells between two numbers like counting the number of cells that fall within determined limits (minimum and maximum) or boundary.

Assume you want to count the number of cells in the Total Sales column containing a number between $1,000 (minimum) and $3,000 (maximum) boundary.

The COUNTIFS formula in Excel =COUNTIFS(E3:E18,”>1000″,E3:E18,”<3000″) results in a total count of 5.

A breakdown of the COUNTIFS formula in Excel:

  • Range1. Cell range F3:F18 is the cells that will be tested against Criteria 1.
  • Criteria1. “>1000” is the defined condition each of the cells in a Range1 is tested against.
  • Range2. Cell range F3:F18 is the second range of cells that will be tested against Criteria2.
  • Criteria2. “<3000” the second criterion to use on Range2.

A similar example is using COUNTIFS function in Excel to count cells between two Dates.

Assume you want to count the number of cells containing dates between Aug 1 to Nov 30. The COUNTIFS formula in Excel =COUNTIFS(B3:B18,”>=01/08/2018″,B3:B18,”<=30/11/2018″) gives you a total count of 5.

5 Takeaways On COUNTIFS Function In Excel

  1. The beauty of the COUNTIFS function in Excel is there is no need for you to sort your list, it is very useful for the UNSORTED list.
  2. The COUNTIFS function in Excel is designed to apply the ‘AND’ logic across various sets of criteria given.
  3. COUNTIFS function in Excel’s Criteria_Range2, Criteria2… pairs must have the same number of rows and columns as Criteria_Range1, Criteria1 pairs.
  4. COUNTIFS works for both contiguous and non-contiguous ranges.
  5. Similar to COUNTIF function, COUNTIFS formula in Excel allows special or wildcard characters such as *, & to filter the data.

Was this helpful?