COUNTIF Across Multiple Sheets in Google Sheets

The COUNTIF function is a fairly basic function in both Excel and Google Sheets. But that doesn’t mean its applications are basic as well.

In this article, we will be looking at a few processes where we will use COUNTIF across multiple sheets in Google Sheets to count values under various circumstances.

Let’s get started.

3 Ways to COUNTIF Across Multiple Sheets or Tabs

To help you move through our processes today, we have created the following Google Sheets spreadsheet.

It contains the Main worksheet, where we will be counting the number of users that use the applications listed here across three different workplaces.

Main worksheet - countif across multiple sheets google sheets

Main Worksheet

Workplace1 Worksheet - countif across multiple sheets google sheets

Workplace1 Worksheet

Workplace2 worksheet - countif across multiple sheets google sheets

Workplace2 Worksheet

Workplace3 worksheet - countif across multiple sheets google sheets

Workplace3 Worksheet

1. Using COUNTIF and SUM (Basic)

We start with a fairly basic and manual process. Here, a table similar to the Main worksheet will be placed in each Workplace worksheet, like so:

bringing table from Main to Workplace worksheets

We will now use the COUNTIF function to count the number of occurrences for each application. Our formula:


Formula Breakdown:

  • $B$3:$D$5: Our range of data in the table. This range has been locked, with absolutes ($) (you can press F4 on your keyboard).
  • B10: Our criterion for COUNTIF. This remains unlocked to refer to the proper cells as we move down the column.

Applying this formula to the rest of the column:

applying countif across multiple sheets google sheets

We do the same for the other Workplace worksheets.

Next, we will be utilizing the SUM function in the Main worksheet to calculate the total number of occurrences across multiple worksheets.

In our cell C3, we type in the formula:


using SUM as a COUNTIF alternative

The formula references each of the application counts in each Workplace worksheet and sums them together to give a total count in the Main worksheet.

Note: Make sure all the references of cells and ranges are correct in each worksheet since this method is not very dynamic.

2. Using COUNTIF OR Logic

Following the same idea that we have used in our last method, we will now be utilizing the COUNTIF function directly on our Main worksheet.

Our summation this time will be following a process called the OR logic. To simplify, we will be adding multiple instances of the COUNTIF function, one for each Workplace worksheet.

Step 1: In cell C3 of our Main worksheet, we will first type in the formula for counting the number of Word occurrences in the Workplace1 worksheet:


Do not press ENTER yet!

referencing another worksheet for countif across multiple sheets google sheets

Our first COUNTIF condition

Step 2: Type + and add the second COUNTIF condition from the Workplace2 worksheet.

adding second COUNTIF

Adding our second COUNTIF condition

Step 3: Type + and add the third COUNTIF condition from the Workplace3 worksheet.

Adding third and final COUNTIF

Our final COUNTIF condition

Step 4: Close parentheses and press ENTER. Apply to the rest of the column using the fill handle.

countif across multiple sheets google sheets with OR logic

Points to Note:

  • The range needs to be locked to avoid wrong cell referencing as we move down the column.
  • We can remove row reference in the range to allow the formula to check the entire column ($B$3:$D$5 to B:D). While it will make the formula slightly more dynamic, you have to be careful about the data you are inputting into your worksheet.

You can have a look at our COUNTIF Multiple Criteria in Google Sheets article for a more in-depth breakdown of the OR logic with COUNTIF.

3. Using COUNTIF Array Version

The method we have just discussed can also be transformed into an array version. This will help to not only reduce the size of the formula but also to organize our references.

Our new formula:


using the ARRAYFORMULA alternative

Formula Breakdown:

  • Since we are working with arrays, Google Sheets requires us to enclose our formula within the ARRAYFORMULA function.
  • We have input all our range references as an array, enclosed in curly braces {}.
  • We now only need a single COUNTIF function as the criterion will be the same for all our ranges.

Final Words

Using COUNTIF to count values across multiple worksheets may have a steep learning curve at the beginning, especially focused on cell references, but that doesn’t diminish the importance of learning this process.

We hope that the methods we have discussed in this article are clear and understandable so that you can utilize them yourself in your spreadsheets. Feel free to let us know any queries or advice you might have in the comments.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply