O365

Vertical column C has unique values, horizontal row 3 has duplicates.

I need to sum up the columns to condense the array as shown in row 9 to 13.

Currently using this formula in D10: =SUMPRODUCT($D$4:$H$7*($D$3:$H$3=D$9)*($C$4:$C$7=$C10))

Every change I make takes 30 sec to calculate even with 16 threads churning. Turning to manual calc is not an option.

I cannot use Pivots or PQ.

The two arrays are quite large, so I'm looking for a faster way to calculate (via formula).

Thoughts: SUM FILTER, SUMIF INDEX MATCH OFFSET

Final Solution @Scott Craner : =SUMIF($D$3:$H$3,D$9,INDEX($D$4:$H$7,MATCH($C10,$C$4:$C$7,0),0))

CodePudding user response：

Use INDEX/MATCH in a SUMIF:

=SUMIF($D$3:$H$3,D$9,INDEX($D$4:$H$7,MATCH($C10,$C$4:$C$7,0),0))

It should be way quicker as it is doing far less calculations.

CodePudding user response：

So, even further to Scott’s formula, you can still squeeze a ton of additional speed out of this, on the condition that C10:C14 is simply equal C4:C7. If they are the same values in the same order then there is no need to do INDEX/MATCH, you can just use a direct relative row reference. This will be profoundly faster yet.

• C10’s formula is =C4:C7 and let it spill.
• D9’s formula is =UNIQUE(D3:H3) and let it spill.
• D10’s formula is =SUMIF($D$3:$H$3, D$9, $D4:$H4). Copy it right and down.