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.

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

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.

• Now, press Enter. You will see the desired result.

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

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.

• Now, on cell E5, write down the following formula.

• Then press Enter. You will have your desired result.

• Here, we have used the SUMPRODUCT and the INDIRECT function along with the COUNTIF function and & operator.

Read More: SUMPRODUCT and COUNTIF Functions with Multiple Criteria

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.

• 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

• After that, write down the following formula in cell E6.

• Then, press the Enter Key, and you will see the desired output.

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

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

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.

• Now, press Enter You will see the correct answer which is 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.

Download Practice Workbook

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

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.