COUNTIFS Array Formula in Google Sheets

COUNTIFS is an array formula function in Google Sheets that returns the count of a range depending on multiple criteria. It is a powerful tool that can dynamically expand the result in most cases, but sometimes it may fail to do so. In this article, we will explore how to overcome such COUNTIFS array formula issues by using other functions, such as LAMBDA or REGEX, in combination with the COUNTIFS function in Google Sheets.

COUNTIFS Function Syntax in Google Sheets

Mastering the syntax of COUNTIFS is essential to fully understand and utilize this function. The syntax is as follows:

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

Now, let’s break down the arguments:

criteria_range1: The first range to check against the criteria (criterion1).
criterion1: The specific criterion that defines 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’s first understand 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 with multiple criteria. These examples use non-array formulas but handle multiple criteria effectively.

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

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

This formula returns the number of titles “Novak Djokovic” won after 2015. In this example, we combine a comparison operator with a number criterion within double quotation marks.

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. Here, we 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.

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. We use a wildcard character, the asterisk (), because the formula might fail to find the condition “Nadal” in B2:B, as the range contains the full name “Rafel Nadal”. The asterisk () wildcard matches any character, including “Nadal” and “Rafel Nadal”.

These examples demonstrate the flexibility of the COUNTIFS function in handling different criteria types effectively.

COUNTIFS Array Formula and Natural Expansion

Most of you know the immediate benefit of using an array 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 that can be expanded using the ARRAYFORMULA or INDEX function. However, in some cases, it may not work as expected.

Example 1: Basic Spill-Down
Consider the following example where we have two criteria and want to get two results corresponding to those criteria. We can use the following COUNTIFS array formula:

=ARRAYFORMULA(COUNTIFS(C2:C,E2:E3))

This formula counts the occurrences of E2 and E3 in column C and returns the results accordingly.

Example 2: Identifying the Same Patterns Using a COUNTIF Array Formula in Google Sheets
We can use a COUNTIFS array formula to find a specific pattern in rows. By analyzing the count results, we can identify if there is the same pattern for one or more employees.

=ARRAYFORMULA(COUNTIFS(D2:D,D2:D,C2:C,C2:C,B2:B,B2:B,E2:E,E2:E))

As per the example, if the COUNTIFS function result is greater than 1, it means there is more than one occurrence of the same pattern, indicating a duplicate row.

These examples demonstrate how to utilize COUNTIFS array formulas effectively to handle various scenarios.

Conclusion

The COUNTIFS function in Google Sheets is a powerful tool that allows you to count cells based on multiple criteria. By understanding its syntax and combining it with other functions like LAMBDA or REGEX, you can overcome limitations and further expand its capabilities.

If you want to dive deeper into advanced COUNTIFS tutorials, check out: