Summing values from different tables

[brijackson205]

Hello,
This is my first post on Mr. Excel and thanks beforehand for any help you can give!
My problem is as follows:
I am working on a workbook to keep track of individual projects in a company. Each project has its own worksheet. In each worksheet I have a chart estimating labor in distinct departments for each week of the project's duration. Each project has distinct begin and end dates. The maximum number of projects is 40.
My first sheet is a summary sheet that will sum labor for all projects for a specific department. I have a timeline that starts at the date of the earliest project and ends at the latest project.

Now my problem is that when I try to use an array formula combined with a lookup algorithm (I have tried HLOOKUP, LOOKUP, and an INDEX/MATCH combination) to sum all labor it always returns a 0 rather than the actual sum.
Examples:

=SUM(IF(ISERROR(HLOOKUP(J$33,INDIRECT("'"&ProjectNamesRange&"'!F32:CP51"),4)),0,HLOOKUP(J$33,INDIRECT("'"&ProjectNamesRange&"'!F32:CP51"),4)))
=SUM(IF(ISERROR(LOOKUP(J$33,INDIRECT("'"&ProjectNamesRange&"'!F32:CP35"))),0,LOOKUP(J$33,INDIRECT("'"&ProjectNamesRange&"'!F32:CP35"))))
=SUM(IF(ISERROR(INDEX(INDIRECT("'"&ProjectNamesRange&"'!F32:CP51"),4,MATCH(Summary!J$33,INDIRECT("'"&ProjectNamesRange&"'!F32:CP32")))),0,INDEX(INDIRECT("'"&ProjectNamesRange&"'!F32:CP51"),4,MATCH(Summary!J$33,INDIRECT("'"&ProjectNamesRange&"'!F32:CP32")))))

Each formula works fine looking as just a normal (not array formula, looking up a value in one specific range), and also appears to work outputting the values as an array (4 values for 4 projects) but fails when I try to sum those values. I also have to put in an if statement that changes any #N/A values from the lookup function to 0 (because it returns #N/A when the date in question is outside the date range of the project). For some reason I do not understand I can never get it to sum the values. Any help will be greatly appreciated!!!

I can supply a sample of my problem upon request.

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

=SUMPRODUCT(SUMIF(INDIRECT("'"&ProjectNamesRange&"'!F32:CP32"),Summary!J$33,INDIRECT("'"&ProjectNamesRange&"'!F35:CP35")))

 

[brijackson205]

It worked great! Thank you for the extremely quick reply. I do have a follow-up question. Why did you use SUMPRODUCT vs just SUM? I tried it with SUM and it worked the same. Is there an advantage to using one or the other?
Again, thanks for the prompt reply!

 

[Andrew Poulsom]

SUMPRODUCT avoids the need to array enter the formula.

 

[brijackson205]

Great, thank you!