Please consider supporting this website by buying me a coffee.
Thank you for your consideration!
Updated | November 3, 2024
Count Based on a Single Criteria Across Multiple Sheets
Summary
Sheet1
Sheet2
Sheet3
The following formula counts the number of times a value in B3:B6 across multiple sheets, starting from Sheet1 to Sheet3 inclusive, equals the value in D3...
=SUMPRODUCT(--(VSTACK('Sheet1:Sheet3'!B3:B6)=D3))
For earlier versions of Excel...
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Summary!$B$3:$B$5&"'!B3:B6"),D3))
...where Summary!B3:B5 contains the sheet names.
Based on the sample data, the formula returns 5.
Sample Workbook: Download