SUMIFS formula help
Results 1 to 5 of 5

Thread: SUMIFS formula help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIFS formula help

    Hi,

    Im having problems with my formula for my database see below

    =SUM(SUMIFS(Data!AA$7:AA102304,Data!D$7:D102304,"*Prim*",Data!H$7:H102304,">=3.01",Data!H$7:H102304,"<=3.50",Data!S$7:S102304,{"AmberH","GreenH"},Data!Z$7:Z102304,{"AmberA","GreenA"}))

    The returns i get from using the formula above do not match the returns when i manually filter the database to verify the return.

    It works OK when there is multiple criteria and just one criteria in either the S and Z ,

    =SUM(SUMIFS(Data!AF$7:AF102303,Data!D$7:D102303,"*Prim*",Data!H$7:H102303,">=5.511",Data!H$7:H102303,"<=6.00",Data!S$7:S102303,{"AmberH","GreenH"},Data!Z$7:Z102303,"*RedA*"))

    =SUM(SUMIFS(Data!AE$7:AE101788,Data!D$7:D101788,"*Prim*",Data!H$7:H101788,">=3.01",Data!H$7:H101788,"<=3.50",Data!S$7:S101788,"*RedH*",Data!Z$7:Z101788,{"AmberA","GreenA"}))

    but i cannot seem to get the formula to work when i need multiple criteria match in two columns. any help appreciated

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,756
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: SUMIFS formula help

    Try this:

    =SUMPRODUCT((ISNUMBER(SEARCH("Prim",Data!D$7:D10)))*(Data!H$7:H10>=3.01)*(Data!H$7:H10<=3.5)*((Data!S$7:S10="AmberH")+(Data!S$7:S10="GreenH"))*((Data!Z$7:Z10="AmberA")+(Data!Z$7:Z10="GreenA"))*(Data!AA$7:AA10))
    Regards Dante Amor

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: SUMIFS formula help

    Modify it as:

    =SUM(SUMIFS(Data!AA$7:AA102304,Data!D$7:D102304,"*Prim*",Data!H$7:H102304,">=3.01",Data!H$7:H102304,"<=3.50",Data!S$7:S102304,{"AmberH","GreenH"},Data!Z$7:Z102304,{"AmberA";"GreenA"}))

    Another option is:

    =SUMPRODUCT(Data!AA$7:AA102304,--ISNUMBER(SEARCH("prim",Data!D$7:D102304)),--(Data!H$7:H102304>=3.01),--(Data!H$7:H102304<=3.50),--ISNUMBER(MATCH(Data!S$7:S102304,{"AmberH","GreenH"},0)),--ISNUMBER(MATCH(Data!Z$7:Z102304,{"AmberA","GreenA"},0)))

    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    New Member
    Join Date
    Dec 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS formula help

    Aladin

    =SUM(SUMIFS(Data!AA$7:AA102304,Data!D$7:D102304,"*Prim*",Data!H$7:H102304,">=3.01",Data!H$7:H102304,"<=3.50",Data!S$7:S102304,{"AmberH","GreenH"},Data!Z$7:Z102304,{"AmberA";"GreenA"}))

    This one works perfectly, thankyou very much

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: SUMIFS formula help

    Quote Originally Posted by juca73 View Post
    Aladin

    =SUM(SUMIFS(Data!AA$7:AA102304,Data!D$7:D102304,"*Prim*",Data!H$7:H102304,">=3.01",Data!H$7:H102304,"<=3.50",Data!S$7:S102304,{"AmberH","GreenH"},Data!Z$7:Z102304,{"AmberA";"GreenA"}))

    This one works perfectly, thankyou very much
    Great. You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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