Excel Formulas - Count number of occurences within mulitple worksheets in a workbook

Count number of occurences within mulitple worksheets in a workbook

Syntax of used function(s)

INDIRECT(ref_text, [a1])
COUNTIF(criteria_range, criteria)
SUMPRODUCT(array1, [array2], [array3], ...)

The INDIRECT function returns the reference specified by a text string. References are immediately evaluated to display their contents.
The COUNTIF function is used to count the number of cells that meet a certain criteria.
The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.

What to do?

To count number of occurences within multiple worksheets of a workbook the SUMPRODUCT function can be used along with COUNTIF and INDIRECT function.

Formula

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!B1:E12"),B6))

How this formula works

In the expression INDIRECT("'"&Sheets&"'!B1:E12"), the "Sheets" is the name of the range B9:B11 which contains 'Sheet1', 'Sheet2', 'Sheet3'. When this expression is evaluated an array creates like -

{"'Sheet1'!B1:E12";"'Sheet2'!B1:E12";"'Sheet3'!B1:E12";}

This workbook has three sheets, and the each sheet contains the table within the range B1:E12. The sheets are below:

The array have three values, each is a text value and contains sheet name joined by cocatenation to the range B1:E12. Therefore the INDIRECT function convert each text value to a proper reference and supply to the criteria_range of the COUNTIF function along with the criteria value B6.

The COUNTIF then return three results form three supplied range and makes an array like

{1;1;1;}

Finally the SUMPRODUCT function produced the sum of the array and returns result.

Previous: Excel Formulas - Count number of rows containing multiple OR condition
Next: Excel Formulas - Count number of rows containing specific value