Table of Contents
The COUNTIF function in Excel can be used to count the number of cells across multiple sheets that meet a certain criteria. By entering the cell range for each sheet along with the criteria in the COUNTIF function, you can quickly count the number of cells that meet the criteria 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”.