Count Based on a Single Criteria Across Multiple Sheets
| Sheet1 | Sheet2 | Sheet3 | Summary |
 |
 |
 |
 |
The following formula counts the number of times a value in F2:F5, across all sheets listed in A2:A4 of the Summary sheet, equals the value in C2 of the Summary sheet...
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!F2:F5"),C2))
Based on the sample data, the formula returns 5.
Sample Workbook: Download