How to Use COUNTIF Function Across Multiple Sheets in Excel

Syntax:

COUNTIF(Where do you want to look?, What do you want to look for?)

This function will return the number of times the set criterion is met within the specified looking range.

Here is the dataset of a List of Countries in Sheet1.

We also have a List of Countries in Sheet2.

There’s also a List of Countries in Sheet3.

We will count the number of times a word (such as Brazil) is found across the three worksheets.

Method 1 – Using an Excel Formula to COUNTIF Across Multiple Sheets

Steps:

In the C5 cell (the result cell), use the following formula.

=COUNTIF(Sheet1!B5:B13,'1. Excel Formula'!B5)+COUNTIF(Sheet2!B5:B13,'1. Excel Formula'!B5)+COUNTIF(Sheet3!B5:B13,'1. Excel Formula'!B5)

Press Enter.

Use of Excel Formula to Countif Across Multiple Sheets

How Does the formula Work❓

COUNTIF(Sheet1!B5:B13,’1. Excel Formula’!B5)

This part searches the Brazil word from B5 of 1. Excel Formula sheet into the range of B5:B13 of Sheet1 and returns the number of times the word is found.

COUNTIF(Sheet2!B5:B13,’1. Excel Formula’!B5)

Searches in Sheet2.

COUNTIF(Sheet3!B5:B13,’1. Excel Formula’!B5)

Searches in Sheet3.

COUNTIF(Sheet1!B5:B13,’1. Excel Formula’!B5)+COUNTIF(Sheet2!B5:B13,’1. Excel Formula’!B5)+COUNTIF(Sheet3!B5:B13,’1. Excel Formula’!B5)

Sums up all those results from the 3 COUNTIF function.

Method 2 – Making a List of Sheet Names to Utilize the COUNTIF Function Across Multiple Sheet

Steps:

In a separate sheet, list the names of the sheets.

Making a List of Name of Sheets to Utilize COUNTIF Function Across Multiple Sheet

In cell E5, use the following formula.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B5:B7&"'!B5:B13"),D5))

Press Enter. You will have your desired result.

Making a List of Name of Sheets to Utilize COUNTIF Function Across Multiple Sheet

How Does the formula Work❓

“‘”&B5:B7

It gives the values of B5:B7 which is the names of sheet (Sheet1; Sheet2;Sheet3).

“‘”&B5:B7&”‘!B5:B13”

This gives the following results Sheet1!B5:B13,Sheet2!B5:B13,Sheet3!B5:B13

Apply COUNTIF for Multiple Criteria

INDIRECT(“‘”&B5:B7&”‘!B5:B13”)

This will result in 3 arrays for 3 datasets of three sheets. As it is not possible to show an array in the formula table, it will show as a Value error.

COUNTIF(INDIRECT(“‘”&B5:B7&”‘!B5:B13”),D5)

This will count the number of times the criterion given on cell D5 (“Brazil”) is met in those three data tables.

SUMPRODUCT(COUNTIF(INDIRECT(“‘”&B5:B7&”‘!B5:B13”),D5))

The SUMPRODUCT function will sum up all those results from the COUNTIF function and give the ultimate result.

Method 3 – Converting Data into Tables to Apply the COUNTIF Function Across Multiple Sheets

Steps:

Select the first dataset and click on the Format as Table option on the ribbon (Alternatively, press Ctrl + T).

After selecting a suitable style, your table should be ready.
Repeat for Sheet2 and Sheet3.
Rename the tables or keep them as-is. We kept the default names: Table1, Table2, and Table3.

Converting Data into Tables to Apply COUNTIF Function Across Multiple Sheets

In a separate sheet, insert the table names in a list and convert it into an Excel table.

Rename the new table or keep the original name. We kept Table4.

Insert the following formula in cell E6.

=SUM(COUNTIF(INDIRECT(Table4[Table List]),D6))

Press the Enter key and you will see the desired output.

Converting Data into Tables to Apply COUNTIF Function Across Multiple Sheets

How Does the formula Work❓

INDIRECT(Table4[Table List]

This will give the array of datasets from the listed Table Names on the Table4 excel table.

COUNTIF(INDIRECT(Table4[Table List]),D6

This will countdown the number of times the condition on D6(Brazil) is satisfied on the 3 tables’ data set)

SUM(COUNTIF(INDIRECT(Table4[Table List]),D6))

The complete formula will sum up all the results and give the final total.

Similar Readings

How to Apply COUNTIF for Multiple Criteria

We have listed dates and will find out the number of dates that are greater than June 1, 2022 but less than June 10, 2022.

Steps:

In cell E6, insert the following formula.

=COUNTIF(B5:B12, ">1/06/2022") - COUNTIF(B5:B12, ">10/6/2022")

Press Enter.

Apply COUNTIF for Multiple Criteria

How Does the formula Work❓

COUNTIF(B5:B12, “>1/06/2022”)

This count the number of dates which is greater than 1st June 2022. The result is 6.

COUNTIF(B5:B12, “>10/6/2022”)

This count the number of dates which is greater than 10th June 2022. The result is 3.

COUNTIF(B5:B12, “>1/06/2022”) – COUNTIF(B5:B12, “>10/6/2022”)

This subtracts the result of the first portion of the formula from the second (6-3=3).

Things to Remember

In Method 2, if the SUM function gives an error in your version of Excel, use the SUMPRODUCT function instead.
While trying Method 2, all the dataset sizes should be equal across the sheets. Furthermore, the positions within the sheet should be identical.
Use Method 1 only when you have a handful of sheets.

Download the Practice Workbook

Countif Multiple Sheet.xlsx