Is there a faster formula? Round / Sumproduct / sumifs / if

[MixedUpExcel]

Hi,

Without me posting a full working example of the sheet / workbook, can anyone point me in a direction of how to speed up this formula?

When I use this, it is across 16 columns x approx. 3,000 rows (this is variable but not by much)

It will effectively be doing 48,000 calculations x how ever many the actual calculations the formula does in one instance.

Formula:

=ROUND(SUMPRODUCT(SUMIFS(Dashboard!$F:$F,Dashboard!$B:$B,Customers!$A3,Dashboard!$D:$D,IF(Customers!$C$1=DATA!$A$2:$A$6,DATA!$B$2:$B$6))),2)

The problem I have is that, although this formula works perfectly, it takes between 15 and 20 minutes to complete.

Any suggestions to speed this up would be appreciated.

Thanks in advance.

Simon

 

[Special-K99]

Care to describe in words what the formula is trying to do?

 

[MixedUpExcel]

Care to describe in words what the formula is trying to do?

Hi Special K99,

I just started to type out what the sheet does and then realized that I am just going to complicate matters further.

I have found a post which says that SumProducts should be used against a full column as it doesn't just calculate the cells you want, it does it against all cells in that column!!

I'm not sure if I can post links to other Forum's - but basically, it's suggested I use a dynamic named range and refer to that named range in the formula.

So basically the following code in a named range called 'sales':

[COLOR=#333333]=[/COLOR][COLOR=#333333]Dashboard[/COLOR][COLOR=#333333]!$F$1:INDEX(Dashboard!$F:$F,COUNTA([/COLOR][COLOR=#333333]Dashboard[/COLOR][COLOR=#333333]!$F:$F))[/COLOR]

So my formula would now look like:

=ROUND(SUMPRODUCT(SUMIFS(sales,Dashboard!$B:$B,Customers!$A3,Dashboard!$D:$D,IF(Customers!$C$1=DATA!$A$2:$A$6,DATA!$B$2:$B$6))),2)

i will do this for all of the references to a complete column on the Dashboard Sheet.

Then I'm going to run it again and see if this speeds things up.

Thanks for getting back to me though.

Simon

 

[steve the fish]

The sumproduct in this instance is only summing the result of 5 sumifs formulas.