SUMIFS vs SUMPRODUCT

[FinAnalyst]

Hi All,

I've got a range (C3:AA5000) that I want to sum. There are also two criteria rows above the range (C1:AA2) and two criteria columns left of the range (A3:B5000).

I am currently using SUMPRODUCT() and it works well, if a little slow. I've been asked to change it to a SUMIFS() as it will be quicker. To my knowledge SUMIFS() will not work for this type of summing.

Is anyone able to provide a definitive answer whether SUMIFS() will or wont work for my scenario? I've pasted an example below.

The goal is to sum to the "Account" level

Thank you in advance.


[TABLE="class: grid, width: 430"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16/12/2015[/TD]
[TD="align: right"]16/01/2016[/TD]
[TD="align: right"]16/12/2015[/TD]
[TD="align: right"]16/01/2016[/TD]
[/TR]
[TR]
[TD]Account[/TD]
[TD]Sub Account[/TD]
[TD]budget[/TD]
[TD]budget[/TD]
[TD]actuals[/TD]
[TD]actuals[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]aa[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]ab[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]ac[/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]195[/TD]
[TD="align: right"]179[/TD]
[TD="align: right"]196[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]ad[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]188[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]ae[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]182[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]af[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]161[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]ag[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]189[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]ah[/TD]
[TD="align: right"]149[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]119[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]ba[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]162[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]149[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]bb[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]154[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]bc[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]179[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]168[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]bd[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]191[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]be[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]154[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]bf[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]161[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]bg[/TD]
[TD="align: right"]189[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]155[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]bh[/TD]
[TD="align: right"]183[/TD]
[TD="align: right"]182[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]141[/TD]
[/TR]
</tbody><colgroup><col><col><col span="4"></colgroup>[/TABLE]

 

[Tetra201]

Yes, SUMIFS() is generally faster than SUMPRODUCT().
What is your SUMPRODUCT formula?

 

[FDibbins]

What was your SP formula and what answer are you expecting?

 

[FDibbins]

Also, it looks like your sub account is unique anyway? Or is that just sample data, is your actual data that different?

and what top criteria would you be using? (which date/s)

 

[FinAnalyst]

Hi All,

Thank you all for the quick responses.


My criteria is: Sum to Account level (a, b, c etc...) by the date (every period) for several years and the type (budget and actuals).

I will also be summing from multiple raw data tabs in the worksheet to a summary tab. Each raw data tab is in the same format as the example I posted. I need to sum each tab to the corresponding Account level.

This is my sumproduct based on the example I posted:

[=SUMPRODUCT($C$3:$F$18,($A$3:$A$18=$S3)*($C$2:$F$2=T$2)*($C$1:$F$1=T$1))/CODE]

Below is the example being summed by Account, Type and Date. This is how I currently do it. 

@FDibbins My actual data has multiple Subaccounts, some that are unique, some that are not. We use the 15th of every month for the period. We are doing analysis on future financial years which is why we use dates. 

 
[TABLE="class: grid, width: 206"]
<tbody>[TR]
[TD][B]S[/B]
[/TD]
[TD][B]T[/B]
[/TD]
[TD][B]U[/B]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]16/12/2015
[/TD]
[TD="align: right"]16/12/2015
[/TD]
[/TR]
[TR]
[TD]Account
[/TD]
[TD]Budget
[/TD]
[TD]Actuals
[/TD]
[/TR]
[TR]
[TD][B]a[/B]
[/TD]
[TD="align: right"]1142
[/TD]
[TD="align: right"]1231
[/TD]
[/TR]
[TR]
[TD][B]b[/B]
[/TD]
[TD="align: right"]1192
[/TD]
[TD="align: right"]1281
[/TD]
[/TR]
</tbody>[/TABLE]


Hope I've explained it clearly enough. Please let me know if I'm not making sense. 

Thank you all :)

 

[FinAnalyst]

Sorry, new to the forum

=SUMPRODUCT($C$3:$F$18,($A$3:$A$18=$S3)*($C$2:$F$2=T$2)*($C$1:$F$1=T$1))

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUMIF($A$3:$A$18,$S3,INDEX($C$3:$F$18,0,MATCH(T$1&T$2,$C$1:$F$1&$C$2:$F$2,0)))