Sum of SUMPRODUCTS

[dhregan]

I have a scheduling worksheet that tracks weekly time. I have a formula that determines the cost associated with time off for a resource within a week on the schedule:

=(SUMPRODUCT((Time_Off!A:A=Estimate!B7)*(Time_Off!D:D=Estimate!C$3))*8*Estimate!C7+SUMPRODUCT((Time_Off!A:A=VLOOKUP(B7,Resources!$A$1:$G$5,4,0))*(Time_Off!D:D=Estimate!C$3))*8*Estimate!C7)*VLOOKUP(B7,RESOURCE_TABLE,7,0)*VLOOKUP(B7,RESOURCE_TABLE,6,0)

I would like to create one formula that will sum all of these weekly time off calculations - spanning from column C through column BK - into one cell BL7 without having to type out a formula that looks like this:

=(SUMPRODUCT((Time_Off!A:A=Estimate!B7)*(Time_Off!D:D=Estimate!C$3))*8*Estimate!C7+SUMPRODUCT((Time_Off!A:A=VLOOKUP(B7,Resources!$A$1:$G$5,4,0))*(Time_Off!D:D=Estimate!C$3))*8*Estimate!C7)*VLOOKUP(B7,RESOURCE_TABLE,7,0)*VLOOKUP(B7,RESOURCE_TABLE,6,0)
+
(SUMPRODUCT((Time_Off!A:A=Estimate!B7)*(Time_Off!D:D=Estimate!D$3))*8*Estimate!D7+SUMPRODUCT((Time_Off!A:A=VLOOKUP(B7,Resources!$A$1:$G$5,4,0))*(Time_Off!D:D=Estimate!D$3))*8*Estimate!D7)*VLOOKUP(B7,RESOURCE_TABLE,7,0)*VLOOKUP(B7,RESOURCE_TABLE,6,0)
+ . . .
(SUMPRODUCT((Time_Off!A:A=Estimate!B7)*(Time_Off!D:D=Estimate!BK$3))*8*Estimate!BK7+SUMPRODUCT((Time_Off!A:A=VLOOKUP(B7,Resources!$A$1:$G$5,4,0))*(Time_Off!D:D=Estimate!BK$3))*8*Estimate!BK7)*VLOOKUP(B7,RESOURCE_TABLE,7,0)*VLOOKUP(B7,RESOURCE_TABLE,6,0)

I have highlighted the range spans in bold red above that I would like to consolidate into a single formula.

 

[oldbrewer]

can I see a bit of data with words explaining what you want please

 

[dhregan]

can I see a bit of data with words explaining what you want please

Here is a snapshot of the full table.

I have highlighted in boxes two examples that compute correctly in columns BM and BN corresponding to data in columns F and AF respectively. Is there a way I can use the SUMPRODUCT formula structure that I referenced so that I can sum across all columns from C through BK and have that single formula present the result in cell BM?

 

[Domenic]

Unfortunately, we are not able to see your snapshot. In any case, maybe...

=SUM(IF(Time_Off!A2:A1000=Estimate!B7,IF(Time_Off!D2:D1000=Estimate!C3:BK3,8*Estimate!C7:BK7))+(IF(Time_Off!A2:A1000=VLOOKUP(B7,Resources!$A$1:$G$5,4,0),IF(Time_Off!D2:D1000=Estimate!C3:BK3,8*Estimate!C7:BK7))))*VLOOKUP(B7,RESOURCE_TABLE,7,0)*VLOOKUP(B7,RESOURCE_TABLE,6,0)

...confirmed with CONTROL+SHIFT+ENTER. Note that this formula is resource intensive. Hence the avoidance of whole column references. If the range for the data increases over time, convert your data into a table. As such, the ranges will automatically adjust as data is added/removed. At some point, though, if there's too much data, you'll run out of resources. I tested it up to 100,000 rows of data and it seemed fine, other than being a bit slow. It might be able handle more.

Hope this helps!

 

[dhregan]

Works perfectly - thanks for the guidance.

 

[Domenic]

You're very welcome!