✦Template Printable Ideas Insights

Mastering CountIF in Google Sheets: Multiple Ranges

By

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.

How to Use COUNTIF on Google Sheets, Infographic
How to Use COUNTIF on Google Sheets, Infographic
Google Sheets Guide: COUNTIF Function
Google Sheets Guide: COUNTIF Function
How To Use COUNTIF In Google Sheets | Formula Quick Guide
How To Use COUNTIF In Google Sheets | Formula Quick Guide

Before we dive into the examples, let's get familiar with the format of this syntax:

COUNTIF({range1, range2, range3, ...}, criteria)

Basic Formatting in Google Sheets
Basic Formatting in Google Sheets
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
How to Create a Cumulative Sum Chart in Google Sheets
How to Create a Cumulative Sum Chart in Google Sheets

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.

The Ultimate Google Sheets Formulas Cheat Sheet
The Ultimate Google Sheets Formulas Cheat Sheet
6 Ways to Use Google Sheets for Data Analysis
6 Ways to Use Google Sheets for Data Analysis
Google Sheets Guide: COUNTIFS Function
Google Sheets Guide: COUNTIFS Function
How to add a Calendar into a cell in Google Sheets #googlesheets #tutorial #spreadsheet
How to add a Calendar into a cell in Google Sheets #googlesheets #tutorial #spreadsheet
How to Protect Sheets and Ranges in Google Sheets
How to Protect Sheets and Ranges in Google Sheets
How to Use COUNTIF and COUNTIFS Functions in Google Sheets
How to Use COUNTIF and COUNTIFS Functions in Google Sheets

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")

How to use an Excel spreadsheet in Google Sheets (access from any device, anytime) or Numbers for Ma
How to use an Excel spreadsheet in Google Sheets (access from any device, anytime) or Numbers for Ma
the google sheets has hidden gems you're not using on your webpages
the google sheets has hidden gems you're not using on your webpages
Master Google Sheets Dynamic Named Ranges
Master Google Sheets Dynamic Named Ranges

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!