Subtotal and Sumif | Need faster calculation than using sumproduct

[mavsounds]

Hi, I got this formula idea from an old thread here. I successfully got the formula to work, but it's very slow. Is there a simpler process?

=IF(A2="","",SUMPRODUCT(SUBTOTAL(9,OFFSET('Partner Planner'!C2,ROW('Partner Planner'!$C:$C)-ROW('Partner Planner'!C2),0)),('Partner Planner'!$A:$A=A2)+0))


Thanks!

 

[BrianGGG]

It’s hard to suggest anything when only the formula is available. It’s hard to picture your exact situation
However, my first guess is that the two references to entire columns is not helping performance.

Is it possible to change the $C:$C to $C2:C1000 (or whatever the situation warranted) ?
Same with the $A:$A ?

If yiu limit both both of these column references does the performance improve ?
If not ... can you describe what you are actually trying to do ?


BrianGGG

 

[pgc01]

Hi

2 options

Option 1 - very bad idea to use whole columns with SumProduct() and Offset().

Use a range just big enough to contain your data, like rows 2:1000


Option 2 - easier and quick: use an auxiliary column to check if 'Partner Planner'!$C:$C is visible.

Ex: use column Z.

In Z2: =SUBTOTAL(9,'Partner Planner'!C2)
Copy down

Then your SumProduct() becomes:

=IF(A2="","",SUMPRODUCT($Z$2:$Z$1000*('Partner Planner'!$A2:$A1000=A2)))

Adjust the last row


Try both and see which is quicker to calculate.

 

[mavsounds]

That did it! Thanks! I limited the column search.