Sum Based on a Single Criteria Across Multiple Sheets
| Sheet1 | Sheet2 | Sheet3 | Summary |
 |
 |
 |
 |
The following formula returns the sum of H2:H5 from each sheet listed in A2:A4 of the Summary sheet, where the corresponding value in G2:G5 equals the value in C2 of the Summary sheet...
=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!G2:G5"),C2,INDIRECT("'"&A2:A4&"'!H2:H5")))
Based on the sample data, the formula returns 150.
Sample Workbook: Download