SUMPRODUCT alternative

[adrienne0914]

I have a workbook with 2000+ lines and 111 columns. As a result, SUMPRODUCT takes forever. My formula on Summary tab is:

=SUMPRODUCT((Source!$C$33:$C$2792=$A2)*(Source!$E$33:$E$2792=$C2)*(Source!$H$32:$DH$32=D$1)*Source!$H$33:$DH$2792)

I tried SUMIFS but got a #VALUE error because arrays are different sizes. Any suggestions, or do you need to see a spreadsheet?

 

[SpillerBD]

The alternative to SUMPRODUCT is usually a Pivot Table. However it looks like you need to work with the Data Model to create the relationships between the Table(s). Additionally you may also need to use PowerPivot for certain calculations.

 

[adrienne0914]

I tried a pivot table. It adds things in ways that are not correct. I was able to use INDEX MATCH MATCH instead. Unfortunately, they don't have PowerPivot turned on at work. *sigh* Thanks for your response.

 

[RoryA]

I was able to use INDEX MATCH MATCH instead.

I take it only one column matches the value in D1 then?

 

[adrienne0914]

I take it only one column matches the value in D1 then?

Not at first, but I added a helper column combining terms, which simplified the formula.