How to countif a specific value across multiple worksheets?
In practical Excel scenarios, it's common to work with workbooks that have data distributed across several worksheets. For instance, you may track monthly sales, customer feedback, or inventory lists, each stored in different sheets within the same file. If you want to find out how many times a particular value - such as the word “Excel” - appears across all those worksheets, it is not straightforward since Excel's built-in functions like COUNTIF only work on a single sheet at a time. Accurately counting occurrences across multiple worksheets can help you perform consolidated reporting, identify trends, or verify consistency in your data.
The following table demonstrates a typical scenario, where the goal is to count the number of times "Excel" appears across several worksheets for a comprehensive result:
 |  |  |  |  |
Countif a specific value across multiple worksheets with formulas
Countif a specific value across multiple worksheets with Kutools for Excel
Countif a specific value across all worksheets with VBA code
Countif a specific value across multiple worksheets with formulas
Excel lets you count occurrences of a value in several worksheets by combining the COUNTIF function and referencing the sheet names dynamically. This method is useful when you have a manageable number of sheets and structured ranges, and when you prefer not to use add-ins or code.
1. First, create a list of all sheet names containing the data you want to analyze. Place this list in a single column, for example, in C2:C4, as illustrated:
You can generate this list manually, or refer to this guide: How to List Worksheet Names in Excel?
2. In any empty cell where you want to show the consolidated count, input the following formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2))After pressing Enter, the cell will display how many times the specified value ("Excel" in this case, entered in E2) appears in the defined range across all listed sheets. See screenshot:
Notes and Tips:
- A2:A6 is the data range on each sheet you wish to search. Adjust this as required for your data layout.
- C2:C4 is the range containing your sheet names. Add more rows as needed if you have additional sheets.
- E2 contains the specific value you want to count. Make sure this matches your data exactly, especially if "Match entire cell" is required.
- If your ranges vary across sheets, this formula may require modification or manual input of separate COUNTIF formulas per sheet, such as:
=COUNTIF(Sheet1!A2:A6,D2)+COUNTIF(Sheet10!A2:A6,D2)+COUNTIF(Sheet15!A2:A6,D2)This method is practical for a limited number of sheets and allows precise control, but becomes cumbersome with many worksheets or changing sheet names.
If you encounter an error (#REF!) with INDIRECT or SUMPRODUCT formulas, double-check sheet names and range references for typos and ensure all sheets exist. All ranges referenced must be valid and consistent.
Countif a specific value across multiple worksheets with Kutools for Excel
With Kutools for Excel, you can streamline the process of counting specific values across multiple worksheets, especially when dealing with a larger dataset or when you need more flexibility. Kutools simplifies complex operations that normally require multiple formulas or manual effort in standard Excel.
After installing Kutools for Excel, please follow these steps:
1. Click Kutools > Navigation, see screenshot:
2. Within the Navigation pane, use the following options for a detailed search and count:
(1.) Expand the Find and Replace pane by clicking its button.
(2.) Enter your target value, such as "Excel", into the Find what text box, ensuring accuracy in spelling and case if exact matches are necessary.
(3.) Select the "Selected Sheets" option from the Within drop-down list.
(4.) From the Workbooks area, select all sheets where you'd like to count occurrences.
(5.) The Match entire cell option ensures only exact matches are counted, which prevents partial or similar strings from being included.
(6.) Click Find All. Results appear in a list, and the total count is displayed at the bottom of the pane. You can browse individual occurrences, making it easy to review the data directly.
If you wish to repeat this process or modify the criteria, simply adjust the Find what value and sheet selection. Using Kutools reduces manual errors and speeds up repetitive tasks. For more details, you may Download and free trial Kutools for Excel Now!
Countif a specific value across all worksheets with VBA code
For users comfortable with automation, using VBA allows you to count the occurrences of a specific value ("Excel") in all worksheets of your workbook in a single action. This is especially useful when you have numerous sheets, varying ranges, or you wish to count dynamically without maintaining formulas or manual lists.
1. Click Developer Tools > Visual Basic to launch the VBA window. In the VBA editor, click Insert > Module, and paste the following code into the module window:
Sub CountValueAcrossAllSheets()
Dim ws As Worksheet
Dim cell As Range
Dim valueToCount As String
Dim totalCount As Long
valueToCount = InputBox("Enter the value to count across all worksheets:", "Count Value", "Excel")
totalCount = 0
For Each ws In ThisWorkbook.Worksheets
For Each cell In ws.UsedRange
If Trim(LCase(cell.Value)) = Trim(LCase(valueToCount)) Then
totalCount = totalCount + 1
End If
Next cell
Next ws
MsgBox "The value '" & valueToCount & "' appears " & totalCount & " times across all worksheets.", _
vbInformation, "Count Complete"
End Sub2. Press the 
button (or press F5) to execute the code. When prompted, enter the value you wish to count (e.g., Excel). The macro will loop through all worksheets and sum the number of matches, providing the consolidated result in a message box.
Note: This method is ideal for large workbooks and lets you search for any value across all worksheets.
Related articles:
How to use countif to calculate the percentage in Excel?
How to countif with multiple criteria in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in