# Sumif, multiple criteria

1. ## Sumif, multiple criteria

I am sure this is simple and just when I thought I knew what I was doing. In the attachment and at the bottom I have a sumifs formula. What I am trying to do is sum based on column M, but then have it not sum based on column b for codes 214000, 214001, & 214500. I have the {} and am using an , to separate, but it's ignoring this and giving me the total based on column M.

Thanks,

2. ## Re: Sumif, multiple criteria

n1 = 214

n3 =if(Left(B3,3)*1=\$N\$1,"NO","yes")

after that a pivot table.

See the attached file.

3. ## Re: Sumif, multiple criteria

No idea if this posted or not

4. ## Re: Sumif, multiple criteria

Perhaps using SUMPRODUCT:
Formula:
`Please Login or Register  to view this content.`

5. ## Re: Sumif, multiple criteria

Originally Posted by Paul
=SUMPRODUCT((A:A=C29)*(M:M="PBG")*(B:B<>214000)*(B:B<>214001)*(B:B<>214500)*(G:G))
Ouch! 7 MILLION cells to be processed by a single formula...

6. ## Re: Sumif, multiple criteria

Paul,
Is there a reason why sumifs won't work? I follow your formula, just curious more than anything?

7. ## Re: Sumif, multiple criteria

oeldere, thanks for the replay, but there's a lot more data in the file and using a pivot table would not be a good solution

8. ## Re: Sumif, multiple criteria

Originally Posted by XOR LX
Ouch! 7 MILLION cells to be processed by a single formula...
Ok, then it's a good thing Excel lets us limit the cell ranges! From 7 million to 7 hundred in the blink of an eye...
Formula:
`Please Login or Register  to view this content.`

9. ## Re: Sumif, multiple criteria

Originally Posted by Paul
Perhaps using SUMPRODUCT:
Formula:
`Please Login or Register  to view this content.`
Paul, any reason sumifs won't work or is it just not preferred?

10. ## Re: Sumif, multiple criteria

SUMIFS is meant to use "AND" logic. It can be used with "OR" logic as seen on this page:
https://exceljet.net/formula/sumifs-...a-and-or-logic

That page also notes that you can use up to two OR criteria using both a horizontal and vertical array, but beyond that you should use SUMPRODUCT. This may be just one OR (with three options), so it might be possible after all, just need to get the syntax correct.

11. ## Re: Sumif, multiple criteria

@tonym33
Just try pivot table. Sooner or later you gonna like it.

12. ## Re: Sumif, multiple criteria

This formula works to get the sum if column B equals the three values you want to exclude, but the trick appears to be getting it to calculate when not equal to those values...
Formula:
`Please Login or Register  to view this content.`

Short of figuring out where to put the "<>" and getting it to work properly, you could subtract the results of the formula above from a SUMIFS of all values matching '11-00004' and 'PBG', like so:
Formula:
`Please Login or Register  to view this content.`

And, as XOR LX noted above, you may want to reduce your ranges from full columns to just the rows you need to scan, e.g. G1:G2000, A1:A2000, M1:M2000, B1:B2000.

13. ## Re: Sumif, multiple criteria

Paul's second above is the most logical and straightforward solution, and also allows you to maintain entire column references (not such an issue with SUMIF(S)/COUNTIF(S) as they are with SUMPRODUCT).

Regards

14. ## Re: Sumif, multiple criteria

This is actually AND logic, not OR logic, so we should also be able to use:

=SUMIFS(G:G,A:A,C29,M:M,"PBG",B:B,"<>214000",B:B,"<>214001",B:B,"<>214500")

However, this falls foul to some inherently twisted logic of COUNTIF(S)/SUMIF(S), i.e. that, whereas, for Paul's

=SUMIFS(G:G,A:A,C29,M:M,"PBG")-SUM(SUMIFS(G:G,A:A,C29,M:M,"PBG",B:B,{214000,214001,214500}))

the 214000, 214001 and 214500 are all recognised as legitimate entries in column B, when coercing them to a text string via concatenation with "<>", i.e. when attempting

=SUMIFS(G:G,A:A,C29,M:M,"PBG",B:B,"<>214000",B:B,"<>214001",B:B,"<>214500")

the result is 55715, not 52393, since this time SUMIFS does not consider those same 3 values to be present in column B.

And the odd thing about this is that it should be precisely the contrary, since the entries in column B are in fact numbers stored as text!

Odder still, this only occurs with 'not equals to': all of the following

=COUNTIF(B3,214000)

=COUNTIF(B3,"214000")

=COUNTIF(B3,"<>214000")

return 1. The 2nd and 3rd surely can't both be TRUE!!

One way to avoid this behaviour is to insert a character which is ignored in text comparisons, viz:

=COUNTIF(B3,"<>"&CHAR(173)&214000)

(See Lori's post near the end here: http://dailydoseofexcel.com/archives...0/countif-bug/)

So, as an alternative to Paul's subtraction-based solution, you could also use:

=SUMIFS(G:G,A:A,C29,M:M,"PBG",B:B,"<>"&CHAR(173)&214000,B:B,"<>"&CHAR(173)&214001,B:B,"<>"&CHAR(173)&214500)

Hardly ideal, though. The lesson to be learnt is perhaps to avoid numbers stored as text!

Regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1