# 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

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)` 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:

=ARRAYFORMULA(COUNTIF({Workplace1!B:D,Workplace2!B:D,Workplace3!B:E},B3)) 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.  