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 preliminary setup, you can use this approach to search an entire workbook for a specific value. In the example shown, the formula in C5 is:
The workbook contains 4 worksheets total. Sheet1, Sheet2, and Sheet3 each contain 1000 random first names that look like this:
The range B7:B9 contains the sheet names we want to include in the search. These are just text strings, and we need to do some work to get them to be recognized as valid sheet references.
Working from the inside out, this expression is used to build a full sheet reference:
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:
which goes into the INDIRECT function as the 'ref_text' argument. INDIRECT 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, you can add wildcards to the criteria like this:
Now COUNTIF will count cells with the substring "John" anywhere in the cell.
In general, it's not a good practice to specify a range that includes all worksheet cells. Doing so can cause 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 ranges to a sensible size. For example, if you know data won't appear past row 1000, you can search just the first 1000 rows like this:
The second sheet in the workbook, Sheet2, contains 1000 first names in the range B4:F203. The COUNTIF function takes a range and a criteria. In this case, we give COUNTIF a range equal to all rows in Sheet2. Sheet2 ! 1 : 1048576 Note: an easy way to...
Working from the inside out, we first have this expression inside the INDIRECT function: "'" & sheets & "'!A1:Z10000" Because "sheets" is a named range that contains "Sheet1", "Sheet2", and "Sheet3", we get an array like...
COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical...
The Excel INDIRECT function returns a valid reference from a given text string. INDIRECT is useful when you want to convert a text value into a valid cell reference.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.