Sumifs with or?

Davestar

New Member
Joined
Apr 5, 2007
Messages
22
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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

It doesn't do anything of the sort! Where in my formula do you see a reference to cells E2, E3, E4, E5 and E6?

Regards
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top