Excel: How to Use COUNTIF Across Multiple Sheets

You can use the following basic syntax to use COUNTIF across multiple multiple sheets in Excel:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!A2:A11"),"Warriors"))

This particular example will count the number of cells in the range A2:A11 that are equal to “Warriors” for each sheet name listed in the range A2:A4.

The following example shows how to use this formula in practice.

Example: How to Use COUNTIF Across Multiple Sheets in Excel

Suppose we have a sheet titled Sheet1 that contains a list of basketball team names:

Suppose we have another sheet titled Sheet2 that contains another list of team names:

And suppose we have another sheet titled Sheet3 that contains another list of team names:

Suppose we would like to use a COUNTIF function to count the number of cells in the range A2:A11 of all three sheets that are equal to “Warriors”.

We can create a new sheet, then list each of the sheet names we’d like to use in the range A2:A4, then type the following formula into cell C2 to count the total number of cells in the first three sheets with values equal to “Warriors”:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!A2:A11"),"Warriors"))

The following screenshot shows how to use this formula in practice:

The formula returns a value of 5, which tells us that there are 5 cells across the first three sheets that are equal to “Warriors”.

Note: To count the number of cells that are equal to a different team name, simply replace “Warriors” in the formula with any team name you’d like.