If you're working with Google Sheets and need to count the number of cells that meet certain criteria across multiple, non-adjacent ranges, the COUNTIF function might seem inadequate. However, with a little creativity and the help of other functions, you can achieve this with ease. Let's delve into the art of using COUNTIF in Google Sheets with multiple ranges.
The key to utilizing COUNTIF across multiple ranges lies in understanding how to apply it in an array. Fear not, for this method is both powerful and surprisingly simple once you've grasped it.
Using COUNTIF with an Array of Ranges
At the heart of this technique is the ability to use COUNTIF with an array of ranges. To do this, we'll generate the array by creating a reference to multiple ranges, then feed that array directly into the COUNTIF function.
Before we dive into the examples, let's get familiar with the format of this syntax:
COUNTIF({range1, range2, range3, ...}, criteria)
Creating the Range Array
To create a range array, you can simply input the different ranges separated by commas within curly braces. For instance, if you want to count cells from A1:A5, B1:B5, and C1:C5, you would type:
{A1:A5, B1:B5, C1:C5}
Applying the Criteria
Once you've created your range array, you can use it with COUNTIF by following the usual syntax: COUNTIF(range, criteria). The criteria can be any text, number, or formula you want to search for within the specified ranges.
Practical Examples
Now that we've laid the groundwork, let's put theory into practice with a couple of examples.
Example 1: Counting Text Values
Suppose you want to count how many times the words "Apple", "Banana", and "Cherry" appear in cells A1:A10, B1:B10, and C1:C10, respectively. You would use:
COUNTIF({A1:A10, B1:B10, C1:C10}, "Apple") + COUNTIF({A1:A10, B1:B10, C1:C10}, "Banana") + COUNTIF({A1:A10, B1:B10, C1:C10}, "Cherry")
Example 2: Counting Dates Within a Range
In this example, let's say you want to count how many dates are between 01/01/2021 and 12/31/2021 in ranges D1:D15, E1:E15, and F1:F15. You would use:
COUNTIF({D1:D15, E1:E15, F1:F15}, ">=01/01/2021") - COUNTIF({D1:D15, E1:E15, F1:F15}, ">12/31/2021")
Congratulations! You've mastered the art of using COUNTIF in Google Sheets with multiple ranges. As you've seen, the key lies in generating an array of ranges and applying the correct criteria. With this newfound skill, you're well on your way to becoming a Google Sheets ninja. Happy counting!