HomeGoogle DocsSpreadsheet

COUNTIFS Array Formula in Google Sheets

Spreadsheet

Published on

Prashanth KV
By Prashanth KV
Modified date:

COUNTIFS is an array formula function in Google Sheets that returns the count of a range depending on multiple criteria.

Unlike SUMIFS in Google Sheets, COUNTIFS can dynamically expand the result in most cases. However, there are certain occasions where it may fail to expand.

In this tutorial, we will see how to overcome such COUNTIFS array formula issues by using other functions, such as a LAMBDA or REGEX, in combination with the COUNTIFS function in Google Sheets.

This post will also explain how to use the COUNTIFS function in Google Sheets.

COUNTIFS Function Syntax in Google Sheets

Mastering the syntax is essential to learning any function. COUNTIFS is no exception.

Syntax:

COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

Arguments:

  • criteria_range1: The first range to check against the criteria (criterion1).
  • criterion1: The criteria in the form of a number/date/time/datetime/text/cell reference/expression that define which cells will be counted in criteria_range1.
  • criteria_range2 (Optional and repeatable): Additional ranges to check.
  • criterion2 (Optional and repeatable): Additional criteria to check.

Before we dive into the COUNTIFS array formula usage in Google Sheets, let me explain how to use this function with a few examples that cover different criteria types.

Basic Formulas: Mastering Multiple Criteria Usage

Here are a few examples of how to use the COUNTIFS function in Google Sheets. All of these examples use non-array formulas, but they handle multiple criteria.

You can make a copy of my sample sheet before you get started.

COUNTIFS Non-Array Formula Examples
Figure 1

1. COUNTIFS with Text and Number Criteria (in cell H3)

=COUNTIFS(B2:B,F3,A2:A,">"&G3)

The formula returns the number of titles “Novak Djokovic” won after 2015.

Comparison operator, if any, with number or date criterion, should be specified within double quotation marks and combined using an ampersand.

In the above formula, all the criteria are cell references. If you want, you can specify them within the formula (hardcode) as follows.

=COUNTIFS(B2:B,"Novak Djokovic",A2:A,">"&2015)

When using criteria within the COUNTIFS function (hardcode), you should enclose them within double quotation marks if they are text.

2. COUNTIFS with Text and Date Criteria (in cell H6)

=COUNTIFS(C2:C,F6,D2:D,">="&G6)

This formula returns the number of titles won by the “United Kingdom” since July 11, 2010.

Hardcoded:

=COUNTIFS(C2:C,"United Kingdom",D2:D,">="&DATE(2010,7,11))

You should specify the text condition within double quotes, the comparison operator within double quotes followed by an ampersand, and the date condition in the DATE(year, month, day) syntax.

I don’t prefer placing the date within double quotes as it may lead to incorrect results in some cases. Use the DATE function as above.

3. COUNTIFS with Text with Wildcard, Number, and Date Criteria (in Cell H9)

=COUNTIFS(B2:B,"*"&F9,A2:A,">"&G9,A2:A,"<"&G10)

This COUNTIFS function returns the number of titles won by Nadal from 2010 to the current date, which is stored in cell G10.

Hardcoded:

=COUNTIFS(B2:B,"*"&"Nadal",A2:A,">"&2009,A2:A,"<"&TODAY())

In this COUNTIFS non-array formula, we have used a wildcard character, which is the asterisk (*).

When using a wildcard character with a criterion in the COUNTIFS function, you must place double quotes around it and join it with the criterion using an ampersand.

I have used a wildcard because the formula might fail to find the condition “Nadal” in B2:B because the range contains the full name “Rafel Nadal”.

The asterisk (*) wildcard will match any character, so it will match “Nadal” as well as “Rafel Nadal”.

You can learn more about criterion use and wildcards by referring to my COUNTIF function guide below.

Must Read: COUNTIF Google Sheets Function: A to Z.

COUNTIFS Array Formula and Natural Expansion

Most of you know the immediate benefit of using an array formula (also known as a spill-down or expanding formula) in Google Sheets. It automatically shows results for newly inserted rows between or at the end of the range.

The COUNTIFS function in Google Sheets is an array formula, and it can spill down by using the ARRAYFORMULA or INDEX function with it. However, in some cases, it won’t work. We will see that after the following COUNTIFS Array Formula examples.

Note: I prefer using the ARRAYFORMULA function over the INDEX function because the INDEX function has other roles in Google Sheets, and this may lead to confusion when reading the formula at a later time.

Example 1: Basic Spill-Down

In the following example, we have two criteria and want to get two results corresponding to the criteria.

So, I’ve used the following COUNTIFS array formula in cell F2:

=ARRAYFORMULA(COUNTIFS(C2:C,E2:E3))
  • criteria_range1: C2:C
  • criterion1: E2:E3
COUNTIFS Array Formula
Figure 2

Example 2: Identifying the Same Patterns Using a COUNTIF Array Formula in Google Sheets

Using a COUNTIFS array formula, we can find a specific pattern in rows.

In the following example, we can go through the count results to understand if there is the same leave pattern for one or more employees.

=ARRAYFORMULA(COUNTIFS(D2:D,D2:D,C2:C,C2:C,B2:B,B2:B,E2:E,E2:E))
Identifying Same Pattern in Rows by Counting
Figure 3

As per the above example, Jeff and Kristi have the same present and absent pattern. We can understand this by checking whether the COUNTIFS function result is greater than 1.

If the COUNTIFS function result is greater than 1, that means there is more than one occurrence of the same pattern. In other words, there is a duplicate row.

This COUNTIFS array formula differs from our basic (not spill-down) formulas in two ways. First, we have used the ARRAYFORMULA function. Second, the criteria (criteria1) is not a single cell, but the same size as the criteria range (criteria_range1).

How Do We Remove 0s (Zeros) in the COUNTIFS Array Formula Result?

In Figure 3, you can see a lot of zeroes in column G. They are returned by the COUNTIFS function in blank rows. How do we remove them?

Use the LET function to name the formula something like a “result” and use an IF statement in the following syntax:

Syntax: IF(result=0, , result)

Here is the COUNTIFS array formula in Google Sheets that excludes zero (0) values:

=ARRAYFORMULA(LET(result,COUNTIFS(D2:D,D2:D,C2:C,C2:C,B2:B,B2:B,E2:E,E2:E),IF(result=0,,result)))

The above ARRAYFORMULA approaches may not work when you use multiple criteria in the same range in COUNTIFS in Google Sheets.

Here are two COUNTIFS function examples to help you learn the correct usage.

COUNTIFS Array Formula and OR (This or That) Criteria in the Second Column

Below, I have fruit names in column B and the date of purchase in column A. How to count the number of purchases of cherries and grapes on a particular date?

We can use the following COUNTIFS function (non-array) for that.

=ARRAYFORMULA(SUM(COUNTIFS(A:A,D2,B:B,{E2,F2})))
COUNTIFS Array Formula with OR Condition
Figure 4

Note: In the above formula, {E2,F2} is an example of OR (this or that) criteria usage in the COUNTIFS function in Google Sheets.

Here, the use of the ARRAYFORMULA function with the COUNTIFS function is not for expanding the result row-wise.

Of course, it will expand the result by returning the count of cherries and grapes on 08/04/2023 in two separate cells. We have used the SUM function to total that result. That’s what you see in cell G2 (please refer to Figure 4 above).

In other words, it helps to expand the first set of criteria in D2:F2. How do we get the count of the items’ cherries and grapes on 08/05/2023 without dragging the G2 formula down?

Incorrect Formula: ✘

=ARRAYFORMULA(SUM(COUNTIFS(A:A,D2:D3,B:B,{E2:E3,F2:F3})))

Correct Formula: ✔

=MAP(D2:D3,E2:E3,F2:F3,LAMBDA(x,y,z, ARRAYFORMULA(SUM(COUNTIFS(A:A,x,B:B,{y,z})))))

We have used the MAP LAMBDA helper function to expand the formula down.

COUNTIFS Array Formula and OR (This or That) Criteria in the Second and Third Columns

Here is a little more advanced COUNTIFS Array Formula example.

I’ve already explained how to use OR in Multiple Columns in COUNTIFS in Google Sheets. That was a non-array formula. Here is an example.

Problem: Count the occurrences of apples or oranges in column C if their status is either In-stock or Ordered in column D on a particular date. The dates are in column B.

We can use the below formula if the date criterion is in cell F2:

=ARRAYFORMULA(
     COUNTIFS(
          REGEXREPLACE(C:C,"(?i)apple|orange","A"),"A",
          REGEXREPLACE(D:D,"(?i)In-stock|Ordered","B"),"B",
          B:B,F2
     )
)

Since OR in multiple columns is a complex task for the COUNTIFS function, we have used REGEXREPLACE.

The REGEXREPLACE function substitutes multiple criteria with a single criterion, which is “A” in the second column and “B” in the third column.

So, the COUNTIFS function can return the count. How do we convert this COUNTIFS formula to an array formula?

Assume you have one more date in cell F3. You don’t need to use the LAMBDA function to expand it. Simply replace cell F2 with cells F2:F3.

REGEXREPLACE with COUNTIFS for Spill Down Result
Figure 5
=ARRAYFORMULA(
     COUNTIFS(
          REGEXREPLACE(C:C,"(?i)apple|orange","A"),"A",
          REGEXREPLACE(D:D,"(?i)In-stock|Ordered","B"),"B",
          B:B,F2:F3
     )
)

Conclusion

I’ve tried to cover every aspect of the COUNTIFS function and its array formula usage in my examples above. These examples take you from basic to advanced usage of the function.

Here are two more advanced COUNTIFS tutorials:

Master them and let us know your experience in learning them.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.
Excel Formula

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...
Excel Formula

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...
Excel Formula

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...
Spreadsheet

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

Spreadsheet

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...
Spreadsheet

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...
Spreadsheet

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.