Mehrab Imtiaz
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
Workplace1 Worksheet
Workplace2 Worksheet
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:
We will now use the COUNTIF function to count the number of occurrences for each application. Our formula:
=COUNTIF($B$3:$D$5,B10)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:
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:
=SUM(Workplace1!C10,Workplace2!C10,Workplace3!C10)
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:
=COUNTIF(Workplace1!$B$3:$D$5,B3)Do not press ENTER yet!
Our first COUNTIF condition
Step 2: Type + and add the second COUNTIF condition from the Workplace2 worksheet.
COUNTIF(Workplace2!$B$3:$D$5,B3)
Adding our second COUNTIF condition
Step 3: Type + and add the third COUNTIF condition from the Workplace3 worksheet.
COUNTIF(Workplace3!$B$3:$E$6,B3)
Our final COUNTIF condition
Step 4: Close parentheses and press ENTER. Apply to the rest of the column using the fill handle.
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:
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.
