We can search **MULTIPLE WORKSHEETS** for **a certain value** and get a **numeric count** for the number of occurrence of the **value** with a formula that integrates the **COUNTIF** and the **INDIRECT** function. The steps below will walk through the process.

*Figure 1: How to Search Multiple Worksheets for Value*

**Formula**

The formula in **Cell C7** is:

**=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)**

**Setting up the Data**

We will set up the data to search for **Peter** and **get a numeric count** by doing the following:

- We will open an
**EXCEL WORKSHEET** - We will type the data into the
**PRIMARY SHEET**,**FIRST****SHEET**,**SECOND SHEET, and THIRD SHEET.**On the**FIRST****SHEET**,**SECOND SHEET, and THIRD SHEET,**we are searching for the number of times the name**PETER**appears. **Note: We have similar data in the FIRST SHEET, SECOND SHEET, and THIRD SHEET**

* Figure 2: Setting up the Data*

**Search Multiple Worksheets for Value**

To search the** first, second and third sheet **for the name** PETER, **we will do the following:

**We will click on Cell C7 in the PRIMARY SHEET**

- We will insert the formula below into
**Cell C7**

**=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)**

**We will press the enter key**

* Figure 3: Search and Count for PETER in the FIRST SHEET*

- We will use the fill handle to drag down and copy the formula into
**Cells C8**and**C9**

* Figure 4: Search and Count for PETER in the FIRST, SECOND and THIRD SHEET*

- We will
**sum the count**by clicking on**Cell C11**and input the formula below

**=SUM(C7:C9)**

- We will click the
**ENTER**button

* Figure 5: Sum of the Number of Occurrences of PETER in the FIRST, SECOND and THIRD SHEET*

**Explanation**

The **INDIRECT function** returns as a text the value in **Cell C4** of the **Primary Sheet**. The **COUNTIF function** counts the number of cells that the text appears in as returned by the **INDIRECT function **and returns the value which is** 5 **for each sheet. The **SUM function** totals the count of the range **C7:C9** to give 15.

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment