How to Use COUNTIF Function Across Multiple Sheets in Excel

The COUNTIF function is a very useful function when it comes to counting the number of cells which obey a specified criterion in an Excel worksheet. But people might find it difficult when applying the function on multiple worksheets at a time. In this article, I will show you how we can use the COUNTIF function across multiple sheets in Excel. So let’s get started.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Useful Methods to Use COUNTIF Function Across Multiple Sheets in Excel

In this section, we will demonstrate 3 effective methods to use the COUNTIF function across multiple sheets in excel with appropriate illustrations. But before that, let’s see the syntax of the COUNTIF function.

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. Now for illustration purposes, we have taken an excel workbook that has 3 data tables across 3 worksheets. This is the dataset of a List of Countries in Sheet1.

Similarly, it is a List of Countries in Sheet2.

Finally, it is a dataset of a List of Countries in Sheet3.

And now, we will try to count the number of times a word(such as Brazil) is found across the three worksheets by using the COUNTIF function.

We can do that using mainly three methods. Let’s look at our first method.


1. Use of Excel Formula to Countif Across Multiple Sheets

This method is a very simple one as here we only apply the COUNTIF function 3 times for each sheet and then sum them together to get our desired result. To know more, follow the steps below.

Steps:

  • In the C5 cell, write down 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)
  • Now, press Enter. You will see the desired result.

Use of Excel Formula to Countif Across Multiple Sheets

  • We can verify the result by manually counting the occurrence of the “Brazil” word.

🎓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)

It does the same thing as the previous one but searches in Sheet2

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

Very similar to the previous two except it 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)

It basically sums up all those results from the 3 COUNTIF function.

Read More: How to Apply COUNTIF Function in Excel with Multiple Criteria


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

The previous method is okay if you have only a handful number of sheets to work with. But for a large number of sheets, this method is really cumbersome. Hence, in that case, we need to follow a more efficient way. To do that, follow the steps below.

Steps:

  • First, in a separate sheet, we will list the name of sheets like this.

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

  • Now, on cell E5, write down the following formula.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&B5:B7&"'!B5:B13"),D5))
  • Then 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 no of times the criterion given on cell D5 (“Brazil”) will be 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


Similar Readings


3. Converting Data into Tables to Apply COUNTIF Function Across Multiple Sheets

The major drawback of the previous method is that every dataset on each sheet needs to be in the same range. Moreover, even if we further add data below any dataset, our result will not be updated as the range which is used while applying the formula is not dynamic. Hence, to get rid of these issues, we can convert those data sets into excel tables. To know more, follow the steps below.

Steps:

  • First, to make all the data set into an excel table, select the dataset first click on the Format as Table option on the ribbon( Alternatively type Ctrl+T).

  • After selecting a suitable style, your table should be ready. Now do the same thing for Sheet2 and Sheet3.
  • After that, we can rename the Tables or keep them as it is. But it is necessary to know the actual names of those 3 tables as we will list them. I kept the default names which are Table1, Table2, and Table3.

Converting Data into Tables to Apply COUNTIF Function Across Multiple Sheets

  • Now, in a separate sheet, write down those Table Names in a List and convert them into an excel table.

  • Here, you can rename the new table or keep the original name. I kept the original name which is

Converting Data into Tables to Apply COUNTIF Function Across Multiple Sheets

  • After that, write down the following formula in cell E6.
=SUM(COUNTIF(INDIRECT(Table4[Table List]),D6))
  • Then, press the Enter Key, and you will see the desired output.

Converting Data into Tables to Apply COUNTIF Function Across Multiple Sheets

  • The most amazing thing about this method is that, even if you add some data below the tables, the data will be automatically incorporated into the table and the result will be updated.

🎓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.

Read More: Apply COUNTIF Function in Multiple Ranges for Same Criteria


How to Apply COUNTIF for Multiple Criteria

Though excel offers a special function for counting cells based on multiple criteria (the COUNTIFS function), still we can use the COUNTIF function. To illustrate that, we have taken some dates, and we will find out the number of dates that are greater than June 1, 2022 but less than June 10, 2022. (see the figure below)

To do that, follow the steps below.

Steps:

  • On cell E6, write down the following formula.
=COUNTIF(B5:B12, ">1/06/2022") - COUNTIF(B5:B12, ">10/6/2022")
  • Now, press Enter You will see the correct answer which is 3.

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).

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 SUMPRODUCT function.
  • While trying the 2nd method, all the dataset sizes should be equal across the sheets. Furthermore, the positions should be identical.
  • Use 1st method only when you have a handful number of sheets.

Conclusion

That is the end of this article. If you find this article helpful in understanding how we can use the COUNTIF function across multiple sheets in excel, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exeldemy for more exciting articles on Excel.


Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I have my Bachelor's degree in Naval Architecture from BUET. I love to read books, listen to podcasts, explore new things, walking randomly in unknown places. Currently, I am working as an Excel and VBA Content Developer. I try to present solutions of problems that are faced in excel in an easy and straightforward manner.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo