How can I use COUNTIF across multiple sheets in Excel?

By /

COUNTIF is a useful function in Excel that allows users to count the number of cells within a range that meet a specific criteria. This function can be further expanded to be used across multiple sheets within a single Excel workbook. By using the syntax “sheetname!range”, users can specify the sheets they want to include in the COUNTIF formula. This allows for a more comprehensive analysis of data in different sheets, making it easier to track and compare information. This feature is particularly useful for large datasets that are spread across multiple sheets, as it eliminates the need to manually search and count data in each sheet separately. By utilizing COUNTIF across multiple sheets, users can efficiently gather and analyze data from different sources within a single Excel file.

Excel: 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”.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. How do I use Excel’s COUNTIF function across multiple sheets?
  2. Google Sheets: Average Across Multiple Sheets
  3. Google Sheets: How do I sum across multiple sheets?
  4. Excel: Sum the Same Cell Across Multiple Sheets
  5. Average Across Multiple Sheets in Excel
  6. How to Use SUMPRODUCT Across Multiple Sheets in Excel
  7. How to Sum Across Multiple Sheets in Excel?
  8. How can I use the INDEX MATCH function to search across multiple columns in Excel?
  9. How to get unique values across multiple columns in Google Sheets?
  10. How to use INDEX MATCH Across Multiple Columns in Excel?