+ Reply to Thread
Results 1 to 14 of 14

Sumif, multiple criteria

  1. #1
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    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,
    Attached Files Attached Files
    Last edited by tonym33; 04-23-2020 at 04:21 PM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    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.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Sumif, multiple criteria

    No idea if this posted or not

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Sumif, multiple criteria

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

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sumif, multiple criteria

    Quote Originally Posted by Paul View Post
    =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...
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Sumif, multiple criteria

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

  7. #7
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    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. #8
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Sumif, multiple criteria

    Quote Originally Posted by XOR LX View Post
    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: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Sumif, multiple criteria

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

  10. #10
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    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. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sumif, multiple criteria

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

  12. #12
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    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: copy to clipboard
    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: copy to clipboard
    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. #13
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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. #14
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Multiple Criteria SUMIF Using Column as Negative Criteria
    By freybe06 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-24-2014, 04:46 PM
  2. SUMIF or SUMIF's formula with multiple criteria
    By jackiemariee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 06:19 PM
  3. Replies: 2
    Last Post: 01-23-2013, 06:25 AM
  4. Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria
    By robgardner15 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2011, 02:35 AM
  5. Sumif multiple criteria
    By darkobird84 in forum Excel General
    Replies: 5
    Last Post: 07-07-2010, 06:19 AM
  6. SUMIF with multiple criteria, one criteria a specific string
    By Weasel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2008, 05:32 PM
  7. SUMIF with multiple criteria
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-06-2005, 05:05 PM
  8. [SOLVED] SUMIF with multiple criteria for multiple columns to sum a single
    By SavageMind in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2005, 11:05 AM

Tags for this Thread

Bookmarks

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