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.
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.
- In cell E5, use the following formula.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&B5:B7&"'!B5:B13"),D5))- Press Enter. You will have your desired result.
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
- 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.
Read More: SUMPRODUCT and COUNTIF Functions with Multiple Criteria
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.
- 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.
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
- COUNTIF with Multiple Criteria in Different Columns in Excel
- How to Apply SUM and COUNTIF for Multiple Criteria in Excel
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.
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).
Read More: Excel COUNTIF Function with Multiple Criteria & Date Range
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
Related Articles
- COUNTIF Between Two Values with Multiple Criteria in Excel
- How to Use COUNTIF Between Two Dates and Matching Criteria in Excel
- How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
- How to Use COUNTIF for Cells Not Equal to Text or Blank in Excel
<< Go Back to COUNTIF Multiple Criteria | Excel COUNTIF Function | Excel Functions | Learn Excel
