< Go Back

Learn How to Count Occurrences in an Entire Workbook

Some functions are simple to use for a worksheet in Excel, but what if you need to do something that spans a whole workbook, such as count items? This tutorial explains how you can count occurrences in an entire workbook.

Count Occurrences in an Entire Workbook in Excel

You can use COUNTIF, and SUMPRODUCT functions to count occurrences in entire workbook.

Generic Formula to Count Occurrence in a Workbook

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!A2:A7"),count_value))

Sheets: named range of the sheets

Count_value: the value you want to count.

How to make a named range:

  • Select the range
  • Type a name into the name box

Figure 1 – How to make a named range

The INDIRECT formula will refer the ranges in each sheet so that the COUNTIF function can count if the values in those ranges match the count_value. The SUMPRODUCT function then sums all the counting numbers relevant to each sheet.    

Example of formula:

Let try to count how many values 1 occur across the workbook

Figure 2 – Count occurrences in the entire workbook

The formula will be =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!A2:A7"),1))

Figure 3 – Count occurrences in the entire workbook

Explanation of this function

The INDIRECT function is very useful to refer the cells in other worksheets. The way to refer another sheet is using sheet’s name, an exclamation mark, and a range reference.

As Sheets is an array,{“sheet1”; “sheet2”; “sheet3”; “sheet4”} the "'"&Sheets&"'!A2:A7" portion will return an array: {"'Sheet1'!A2:A7";"'Sheet2'!A2:A7";"'Sheet3'!A2:A7";"'Sheet4'!A2:A7"}

INDIRECT function based on this array to refer the ranges A2:A7 in each sheet.

Next, the COUNTIF function goes through each range A2:A7 in each sheet to match the provided value 1. The result of counting is put in an array {2,1,1,2}. Each number is the occurrences of value 1 in each sheet.

Finally, the SUMPRODUCT function sums all the number in the array and returns 6. This is the occurrences of value 1 across the workbook.

Note

The count_value can be text or numeric values.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar