Explanation
The range B7:B9 contains the sheet names we want to include in the search.
The text “1:1048576” is a range that includes every row in the worksheet.
INDIRECT evaluates this text and returns a standard reference to every cell inSheet1.
This goes into the COUNTIF function as the range.
Note: COUNTIF is not case-sensitive.
Contains vs.
Doing so can cause performance problems, since the range includes millions and millions of cells.
Volatile functions recalculate on every worksheet change, so the impact on performance can be huge.
When possible, restrict ranges to a sensible size.
INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
