# Thread: Sumifs with or? Thanks: 0 Likes: 0

1. ## Sumifs with or?

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.

2. ## Re: Sumifs with or?

Hi.

=SUMPRODUCT(SUMIFS(C:C,A:A,E1,B:B,F1:F6))

Regards

3. ## Re: Sumifs with or?

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

4. ## Re: Sumifs with or?

Originally Posted by Davestar
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

5. ## Re: Sumifs with or?

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?

6. ## Re: Sumifs with or?

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

7. ## Re: Sumifs with or?

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.

8. ## Re: Sumifs with or?

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!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•