< Go Back

Search Multiple Worksheets for Value

How to Search Multiple Worksheets for Value

If you have MULTIPLE WORKSHEETS that contain several VALUES, how will you check for the number of times that a particular value appears on ALL WORKSHEETS?

If you want to SEARCH MULTIPLE WORKSHEETS for a value in excel and get a numeric value, a formula which integrates the COUNTIF and INDIRECT function can be used.

Search Multiple Worksheets for Value

Formula

In the example below, the formula in Cell C7 is:

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

The syntax for this formula is:

=COUNTIF(range, criteria)

Explanation

The value in Cell C4 is searched for in the MULTIPLE SHEETS and the COUNTIF function displays the result numerically for all Cells that fulfills the CRITERIA PER SHEET. After the count for the FIRST SHEET has been returned, the cursor automatically moves to the next cell on the same column. You can calculate the count for that column by changing B7 in the formula above to B8 and so on.

Example

STEP 1

    • Open an EXCEL WORKSHEET
  • 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.

 

Figure 1- How to Search Multiple Worksheets for Value

  • For this example, similar data have been used for the FIRST SHEET, SECOND SHEET, and THIRD SHEET

Figure 2- How to Search Multiple Worksheets for Value

STEP 2

  • Click on Cell C7 on the PRIMARY SHEET
  • Type or paste the string below into the Cell

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

Figure 3- How to Search Multiple Worksheets for Value

  • Click on the ENTER button
  • Your result will be similar to figure 4

Figure 4- How to Search Multiple Worksheets for Value

STEP 3

  • Click on Cell C8 on the PRIMARY SHEET
  • Change B7 in the string below to B8 and type or paste the string into the cell

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

  • When you click the ENTER button, your result should be like figure 5

Figure 5- How to Search Multiple Worksheets for Value

 

  • With a similar approach, we can get the count for the THIRD SHEET
  • To sum the result, highlight Cell C11 and use the string below

=SUM(C7:C9)

  • Click the ENTER button and your result should be like figure 6

Figure 6- How to Search Multiple Worksheets for Value

 

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

Leave a Comment

avatar