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.

A	B	C	D	E	F
		16/12/2015	16/01/2016	16/12/2015	16/01/2016
Account	Sub Account	budget	budget	actuals	actuals
a	aa	154	169	139	156
a	ab	131	176	120	120
a	ac	148	195	179	196
a	ad	163	188	169	123
a	ae	171	187	172	182
a	af	111	172	126	161
a	ag	115	189	154	130
a	ah	149	133	172	119
b	ba	166	162	180	149
b	bb	110	171	132	154
b	bc	103	179	192	168
b	bd	124	108	116	191
b	be	147	174	136	154
b	bf	170	137	163	161
b	bg	189	170	192	155
b	bh	183	182	170	141

<tbody>
</tbody><colgroup><col><col><col span="4"></colgroup>

 

[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)))