INDIRECT

Count occurrences in entire workbook

Count occurrences in entire workbook The following formula is used to count occurrences in entire workbook. Place the worksheet list and search text in B1 cell. The cells referred in sheets range is A1:A5. Sheets is named range, where list of sheets are referred. =SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!"&rng),criteria)) =SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:A5"),B1))