using SUMIFS across multiple sheets

[sarmk28]

Hi, I am trying to add up hours worked on specific projects over different sheets and with multiple criteria. For example, I want excel to recognize and add up all of the hours spent with criteria "company" and "project name", over 6 different sheets. Company is listed in all sheets under Column B (B4:B90) and Project name is listed under C (C4:C90), the actual hours to be added are under H4:H90. I have tried using SUMIFS, as well as SUMPRODUCT but have not succeeded. Please help! Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

Make a list of the names of the sheets that you want to sum and name it Sheets. Then with Company criterion in A1 and Project criterion in B1 try:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!H4:H90"),INDIRECT("'"&Sheets&"'!B4:B90"),A1,INDIRECT("'"&Sheets&"'!C4:C90"),B1))

 

[sarmk28]

Thank you! Okay, I tried that but it says "#REF!" I'm doing the calculation in a separate sheet in the same workbook. I defined the word Sheets as Sheet1!:Sheet7!Is that ok?

 

[sarmk28]

Any ideas about what I can do next? Thanks!

 

[Andrew Poulsom]

Make a list of the sheet names like this:

Excel 2010
	H
1	Sheet1
2	Sheet2
3	Sheet3
4	Sheet4
5	Sheet5
6	Sheet6
7	Sheet7
Summary

Select those cells, got to the Name box to the left of the Formula Bar, type Sheets and press Enter. Then try the formula I posted.

 

[sarmk28]

AMAZING! it works. Thank you so much!