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

portion will return an array: **"'"&Sheets&"'!A2:A7" **`{"'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.

## Leave a Comment