Search multiple worksheets for value

Search multiple worksheets for value

Generic formula 

=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)

Related formulas 

Search entire worksheet for value

Count occurrences in entire workbook

Explanation

To search multiple worksheets in a workbook for a value and return a count, you can use a formula based on the COUNTIF and INDIRECT functions. With some setup, you use this approach to search an entire workbook for a specific value.

In the example shown, the formula in C5 is:

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

Context - sample data

The workbook contains 4 worksheets total. Sheet1, Sheet2, and Sheet3 each contain 1000 random first names that look like this:

How this formula works

The range B7:B9 contains the Sheets we want to count.

Working from the inside out, this expression is used to build a full sheet reference:

"'"&B7&"'!"&"1:1048576"

The single quotes are added to allow sheet names with spaces, and the exclamation mark is a standard syntax for ranges that include a sheet name. The text "1:1048576" is a range that includes every row in the worksheet.

After B7 is evaluated, and values are concatenated, the expression above returns:

"'Sheet1'!1:1048576"

which is goes into the INDIRECT function as the 'ref_text' argument. INDIRECT then evaluates this text and returns a standard reference to every cell in Sheet1. This goes into the COUNTIF function as the range. The criteria is provided as an absolute reference to C4 (locked so the formula can be copied down column C).

COUNTIF then returns a count of all cells with a value equal to "mary", 25 in this case.

Note: COUNTIF is not case-sensitive.

Contains vs. Equals

If you want to count all cells that contain the value in C4, instead of all cells equal to C4, add the wildcards to the criteria like this:

=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")

Now COUNTIF will count cells with the substring "John" anywhere in the cell.

Performance

In general, it's not a good practice to specify a range that includes all worksheet cells. Doing so can cause major performance problems, since the range includes millions and millions of cells. In this example, the problem is compounded, since the formula uses the INDIRECT function, which is a volatile function. Volatile functions recalculate on every worksheet change, so the impact on performance can be huge.

When possible, restrict all ranges to a sensible size.

Search multiple worksheets for value

Generic formula 

=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)

Related formulas 

Search entire worksheet for value

Count occurrences in entire workbook

Explanation

To search multiple worksheets in a workbook for a value and return a count, you can use a formula based on the COUNTIF and INDIRECT functions. With some setup, you use this approach to search an entire workbook for a specific value.

In the example shown, the formula in C5 is:

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

Context - sample data

The workbook contains 4 worksheets total. Sheet1, Sheet2, and Sheet3 each contain 1000 random first names that look like this:

How this formula works

The range B7:B9 contains the Sheets we want to count.

Working from the inside out, this expression is used to build a full sheet reference:

"'"&B7&"'!"&"1:1048576"

The single quotes are added to allow sheet names with spaces, and the exclamation mark is a standard syntax for ranges that include a sheet name. The text "1:1048576" is a range that includes every row in the worksheet.

After B7 is evaluated, and values are concatenated, the expression above returns:

"'Sheet1'!1:1048576"

which is goes into the INDIRECT function as the 'ref_text' argument. INDIRECT then evaluates this text and returns a standard reference to every cell in Sheet1. This goes into the COUNTIF function as the range. The criteria is provided as an absolute reference to C4 (locked so the formula can be copied down column C).

COUNTIF then returns a count of all cells with a value equal to "mary", 25 in this case.

Note: COUNTIF is not case-sensitive.

Contains vs. Equals

If you want to count all cells that contain the value in C4, instead of all cells equal to C4, add the wildcards to the criteria like this:

=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")

Now COUNTIF will count cells with the substring "John" anywhere in the cell.

Performance

In general, it's not a good practice to specify a range that includes all worksheet cells. Doing so can cause major performance problems, since the range includes millions and millions of cells. In this example, the problem is compounded, since the formula uses the INDIRECT function, which is a volatile function. Volatile functions recalculate on every worksheet change, so the impact on performance can be huge.

When possible, restrict all ranges to a sensible size.