How to countif a specific value across multiple worksheets?

Supposing, I have multiple worksheets which contains following data, and now, I want to get the number of occurrence of a specific value “Excel” from theses worksheets. How could I count a specific values across multiple worksheet?

Countif a specific value across multiple worksheets with formulas

Countif a specific value across multiple worksheets with Kutools for Excel

Combine multiple worksheets, workbooks and csv files into one worksheet / workbook:

In your daily work, to combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge and headachy work. But, if you have Kutools for Excel, with its powerful utility – Combine, you can quickly combine multiple worksheets, workbooks into one worksheet or workbook.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Countif a specific value across multiple worksheets with formulas

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

In Excel, there is a formula for you to count a certain values from multiple worksheets. Please do as follows:

1. List all the sheet names which contain the data you want to count in a single column like the following screenshot shown:

doc-count-across-multiple-sheets-5

2. In a blank cell, please enter this formula: =SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2)), then press Enter key, and you will get the number of the value “Excel” in these worksheets, see screenshot:

doc-count-across-multiple-sheets-6

Notes:

1. In the above formula:

A2:A6 is the data range that you want to count the specified value across worksheets;
C2:C4 is the sheet names list which include the data you want to use;
E2 is the criteria that you want based on.

2. If there are multiple worksheets need to be listed, you can read this article How to List Worksheet Names in Excel? to deal with this task.

3. In Excel, you can also use the COUNTIF function to add the worksheet one by one, please do with the following formula: =COUNTIF(Sheet1!A2:A6,D2)+COUNTIF(Sheet10!A2:A6,D2)+COUNTIF(Sheet15!A2:A6,D2), (Sheet1, Sheet10 and Sheet15 are the worksheets that you want to count, D2 is the criteria that you based on), and then press Enter key to get the result. See screenshot:

doc-count-across-multiple-sheets-7

Countif a specific value across multiple worksheets with Kutools for Excel

If you have Kutools for Excel, with its Navigation pane, you can quickly list and count the specific value across multiple worksheet.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

Go to Download
Free Trial 60 days Purchase
PayPal / MyCommerce

After installing Kutools for Excel, lease do as follows:

1. Click Kutools > Navigation, see screenshot:

doc-count-across-multiple-sheets-9

2. In the Navigation pane, please do the following operations:

(1.) Click Find and Replace button to expand the Find and Replace pane;

(2.) Type the specific value into the Find what text box;

(3.) Choose a search scope from the Whithin drop down, in this case, I will choose Selected Sheet;

(4.) Then select the sheets which you want to count the specific values from the Workbooks list box;

(5.) Check Match entire cell if you want to count the cells match exact;

(6.) Then click the Find All button to list all the specific values from multiple worksheets, and the number of the cells are displayed at the bottom of the pane.

Download and free trial Kutools for Excel Now !

Demo: Countif a specific value across multiple worksheets with Kutools for Excel

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Related articles:

How to use countif to calculate the percentage in Excel?

How to countif with multiple criteria in Excel?

Recommended Productivity Tools

Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

Merge Cell/Rows/Columns without Losing Data.
Combine and Consolidate Multiple Sheets and Workbooks.
Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

9 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest ( Sign Up? )

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
emran · 2 months ago
1*1=1 but from 2 it should be 3 double. how i set up in excel? please give me a solution.
- Reply
- Share
To post as a guest, your comment is unpublished.
Mike · 1 years ago
This was extremely helpful. the below formula is what i use to track all my employees Vacation,Sick,Personal,LOA days. I have tabs for every month of the year and use this to track how many days they have used. All i do is change the "V" to a "S" or "P". Formula works perfect! thanks for the info....

=COUNTIF(January!B16:AF16,"V")+COUNTIF(February!B16:AF16,"V")+COUNTIF(March!B16:AF16,"V")+COUNTIF(April!B16:AF16,"V")+COUNTIF(May!B16:AF16,"V")+COUNTIF(June!B16:AF16,"V")+COUNTIF(July!B16:AF16,"V")+COUNTIF(August!B16:AF16,"V")+COUNTIF(September!B16:AF16,"V")+COUNTIF(October!B16:AF16,"V")+COUNTIF(November!B16:AF16,"V")+COUNTIF(December!B16:AF16,"V")
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Hari · 6 months ago
  
  I want to count specific word in Coloum accross the Jan to Dec, Please advice and sum it up
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
David · 1 years ago
Hi. Using this formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2))

I get a Ref error if Column C has more than 9 rows. In other words, INDIRECT("'"&C2:C9&"'!A2:A6"),E2)), is OK but INDIRECT("'"&C2:C10&"'!A2:A6"),E2)) is not.
How do I avoid this error?
- Reply
- Share
To post as a guest, your comment is unpublished.
foxx · 2 years ago
How would I write the formula above (=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2))) if I had to use the COUNTIFS function? Right now I have to see if certain cells have met 2 to 3 criteria (across multiple worksheets).

cheers for you help.
- Reply
- Share
- To post as a guest, your comment is unpublished.
  matt · 1 years ago
  
  try =SUMPRODUCT(COUNTIFS(INDIRECT("'"&C2:C4&"'!A2:A6"),E2),(INDIRECT("'"&C2:C4&"'!A2:A6"),X2),(INDIRECT("'"&C2:C4&"'!A2:A6"),Y2))
  where E2, X2 and Y2 are your criteria and INDIRECT("'"&C2:C4&"'!A2:A6" are your criteria range, where C2:C4 are your tab names and A2:A6 is your data range
  
  let me know if it works I'm actually very curious, didn't have time to run and test it myself. I just had to use one criteria so above easier formula did work wonders for me
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
juntjoo · 3 years ago
Can you not specify your range of cells within a RANGE of sheets instead of "sheet1+sheet2+sheet3...." and specify a range to compare to like: countif(sheet1:sheet5!b:b, a:a)
- Reply
- Share
- To post as a guest, your comment is unpublished.
  matt · 1 years ago
  
  tried it, didn't work. you need your tab(sheet) names in the cells for it to work. The cells are actually making up a range themselves, hence C2:C4 in the formula
  
  Reply
  
  Share

Login to your account

Password Reset

Reminder