Hi.
=SUMPRODUCT(SUMIFS(C:C,A:A,E1,B:B,F1:F6))
Regards
I have a spend value in column C that is associated with a Costs Centre in column A and a nominal code in column B.
I then have a series of costs centres in cells E1:E6 and list of nominal codes in F1:F6.
I want the sum of spend for all combinations of Cost Centre and Nominal Code entered in E1:F6.
I know I can do this with multiple Sumifs, but is there a neater way to do this?
i.e. =Sum(Sumifs(C:C,A:A,E1,B:B,F1),Sumifs(C:C,A:A,E1,B:B,F2),Sumifs(C:C,A:A,E1,B:B,F3),Sumifs(C:C,A:A,E1,B:B,F4),Sumifs(C:C, A:A,E1,B:B,F5),Sumifs(C:C,A:A,E1,B:B,F6),Sumifs(C:C,A:A,E2,B:B,F1)... Sumifs(C:C,A:A,E6,B:B,F6)
Any help gratefully appreciated.
Hi.
=SUMPRODUCT(SUMIFS(C:C,A:A,E1,B:B,F1:F6))
Regards
Advanced Excel Techniques: http://excelxor.com/
Thanks for such a quick response, but the formula doesn't seem to work. I think I've not explained what I am looking for sufficiently clearly.
The formula you've given calculates the sum of the sumifs as follows:
A:A = E1 AND B:B = F1
A:A = E2 AND B:B = F2
A:A = E3 AND B:B = F3
A:A = E4 AND B:B = F4
A:A = E5 AND B:B = F5
A:A = E6 AND B:B = F6
I am looking to calculate as follows:
A:A = E1 AND B:B = F1
A:A = E1 AND B:B = F2
A:A = E1 AND B:B = F3
A:A = E1 AND B:B = F4
A:A = E1 AND B:B = F5
A:A = E1 AND B:B = F6
A:A = E2 AND B:B = F1
A:A = E2 AND B:B = F2
A:A = E2 AND B:B = F3
A:A = E2 AND B:B = F4
A:A = E2 AND B:B = F5
A:A = E2 AND B:B = F6
A:A = E3 AND B:B = F1
A:A = E3 AND B:B = F2
A:A = E3 AND B:B = F3
A:A = E3 AND B:B = F4
A:A = E3 AND B:B = F5
A:A = E3 AND B:B = F6
A:A = E4 AND B:B = F1
A:A = E4 AND B:B = F2
A:A = E4 AND B:B = F3
A:A = E4 AND B:B = F4
A:A = E4 AND B:B = F5
A:A = E4 AND B:B = F6
A:A = E5 AND B:B = F1
A:A = E5 AND B:B = F2
A:A = E5 AND B:B = F3
A:A = E5 AND B:B = F4
A:A = E5 AND B:B = F5
A:A = E5 AND B:B = F6
A:A = E6 AND B:B = F1
A:A = E6 AND B:B = F2
A:A = E6 AND B:B = F3
A:A = E6 AND B:B = F4
A:A = E6 AND B:B = F5
A:A = E6 AND B:B = F6
Advanced Excel Techniques: http://excelxor.com/
Okay sorry, it doesn't. I tried to amend it.
=SUMPRODUCT(SUMIFS(C:C,A:A,E1,B:B,F1:F6)) does
A:A = E1 AND B:B = F1
A:A = E1 AND B:B = F2
A:A = E1 AND B:B = F3
A:A = E1 AND B:B = F4
A:A = E1 AND B:B = F5
A:A = E1 AND B:B = F6
if I amend to =SUMPRODUCT(SUMIFS(C:C,A:A,E1:E6,B:B,F1:F6))
it does:
A:A = E1 AND B:B = F1
A:A = E2 AND B:B = F2
A:A = E3 AND B:B = F3
A:A = E4 AND B:B = F4
A:A = E5 AND B:B = F5
A:A = E6 AND B:B = F6
I want it to loop through to do all the possible combinations as stated in my second post. Is that possible?
I see.
You can either use an array formula**:
=SUM(SUMIFS(C:C,A:A,TRANSPOSE(E1:E6),B:B,F1:F6))
or make it so that your two criteria ranges are orthogonal (i.e. one is a single-column array, the other a single-row array): for example, if you make it so that the values in F1:F6 are instead in e.g. F1:K1, then you can use the following, which does not require committing with CSE:
=SUMPRODUCT(SUMIFS(C:C,A:A,E1:E6,B:B,F1:K1))
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Advanced Excel Techniques: http://excelxor.com/
That's grand. They both work perfectly. I didn't realise that you needed orthogonal arrays to work the sumproduct/sumifs formulae. Thanks for your help.
You don't always - only if you're applying multiple OR conditions to two of your criteria_ranges.
See here if you're interested in a technical explanation:
COUNTIFS: Multiple “OR” criteria for one or two criteria_Ranges « EXCELXOR
And you're welcome!
Advanced Excel Techniques: http://excelxor.com/
Like this thread? Share it with others