Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts

    SUMIFS with multiple sum_range & multiple criteria ALSO 1 criteria range has two acceptable criteria

    I'm using Excel 2016
    I'm trying to make the following formula more condensed so modifying it and keeping it updated is easier

    Code:
    =SUMIFS(violation,transR,"Completed",transT,"payment",paymentT,"Visa")
    +SUMIFS(violation,transR,"Completed",transT,"payment",paymentT,"Mastercard")
    +SUMIFS(violationF,transR,"Completed",transT,"payment",paymentT,"Visa")
    +SUMIFS(violationF,transR,"Completed",transT,"payment",paymentT,"Mastercard")
    +SUMIFS(violationPF,transR,"Completed",transT,"payment",paymentT,"Visa")
    +SUMIFS(violationPF,transR,"Completed",transT,"payment",paymentT,"Mastercard")
    Code:
    =SUM(SUMIFS(violation,transR,"Completed",transT,"payment",paymentT,{"Visa","mastercard"}))
    +SUM(SUMIFS(violationF,transR,"Completed",transT,"payment",paymentT,{"Visa","mastercard"}))
    +SUM(SUMIFS(violationPF,transR,"Completed",transT,"payment",paymentT,{"Visa","mastercard"}))
    this still leaves me with 3 Functions one for each sum range. Is there a way to combine the three?
    Last edited by macropod; 2016-03-15 at 18:56. Reason: Moderation Repairs

  2. #2
    Super Moderator satrow's Avatar
    Join Date
    Dec 2009
    Location
    Cardiff, UK
    Posts
    4,484
    Thanks
    283
    Thanked 572 Times in 476 Posts
    Hi Matthew, sorry but we're having a technical issue trying to make your post public, as a workaround, I've quoted it below (with some edits that we tried):



    Quote Originally Posted by Matthew Brady View Post
    I'm using Excel 2016
    I'm trying to make the following formula more condensed so modifying it and keeping it updated is easier

    Code:
    =SUMIFS(violation,transR,"Completed",transT,"payment",paymentT,"Visa")
    +SUMIFS(violation,transR,"Completed",transT,"payment",paymentT,"Mastercard")
    +SUMIFS(violationF,transR,"Completed",transT,"payment",paymentT,"Visa")
    +SUMIFS(violationF,transR,"Completed",transT,"payment",paymentT,"Mastercard")
    +SUMIFS(violationPF,transR,"Completed",transT,"payment",paymentT,"Visa")
    +SUMIFS(violationPF,transR,"Completed",transT,"payment",paymentT,"Mastercard")
    Code:
    =SUM(SUMIFS(violation,transR,"Completed",transT,"payment",paymentT,{"Visa","mastercard"}))
    +SUM(SUMIFS(violationF,transR,"Completed",transT,"payment",paymentT,{"Visa","mastercard"}))
    +SUM(SUMIFS(violationPF,transR,"Completed",transT,"payment",paymentT,{"Visa","mastercard"}))
    this still leaves me with 3 Functions one for each sum range. Is there a way to combine the three?

  3. The Following User Says Thank You to satrow For This Useful Post:

    Matthew Brady (2016-03-17)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Matthew,

    Welcome to the Lounge as a new poster!

    When you get into complicated SumIfs like that it might pay dividends to explore the DSUM function as you can setup a criteria page with multiple lines (one for each credit card) with the other criteria adjust as necessary. It will make things a lot simpler.

    If you could make a TEST worksheet that you could post it would give us a better idea what you are doing and allow us to provide more specific help.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Matthew Brady (2016-03-17)

  6. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could do it with INDIRECT, but it will make the formula volatile:

    =SUM(SUMIFS(INDIRECT({"Violation";"ViolationF";"Vi olationPF"}),TransR,"Completed",TransT,"payment",p aymentT,{"Visa","mastercard"}))
    Regards,
    Rory

    Microsoft MVP - Excel

  7. The Following 2 Users Say Thank You to rory For This Useful Post:

    Matthew Brady (2016-03-17),zeddy (2016-03-17)

  8. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Mathew

    see attached example file (this has ~500 records)

    I have entered your original formula, rory's formula, my formula, and RG's formula to show the same results using the different methods.

    My formula requires an additional 'helper' column "amount"
    =SUMIFS(amount,transR,"completed",transT,"payment" )

    RG's formula is shortest:
    =DSUM(dataBlock,"amount",critBlock)

    Pick and choose the formula that is the most comfortable for you.

    You could also get the result by filtering the data records to suit.


    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2016-03-16 at 14:30. Reason: fixed formula in cell [G2]

  9. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    Matthew Brady (2016-03-17),RetiredGeek (2016-03-16)

  10. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    Nice work as always! However, me thinks you're slipping? You missed one option, e.g. my solution w/o the extra column.

    Code:
    =DSUM(dataBlock,"violation",critBlock)+DSUM(dataBlock,"violationF",critBlock)+DSUM(dataBlock,"violationPF",critBlock)
    Same results, but it is a longer formula. Don't you just love Excel

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2016-03-17)

  12. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi RG

    ..slipping?? I was going for a nice looking formula that was easy to read and didn't hurt my head. With a few more helper columns we could get:
    =SUM(amount)
    ..and we haven't even touched VBA for an answer yet, or maybe even make use of that fancy new Excel2016 function =IFS(..

    zeddy

  13. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Matthew,

    I have added my formula using SumProduct to zeddy's comparative worksheet. Ranges renamed not to include the header and does not use a helper column.

    =SUMPRODUCT((vio+vioF+vioPF)*((pay="Visa")+(pay="m astercard"))*(Comp="completed")*(pymt="payment"))

    HTH,
    Maud

    zeddy3.png
    Attached Files Attached Files

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2016-03-17)

  15. #9
    New Lounger
    Join Date
    Feb 2010
    Location
    East Brunswick, NJ
    Posts
    15
    Thanks
    1
    Thanked 3 Times in 2 Posts
    Matthew,
    I've nowhere near the experience that the other posters have, but I like to play with pivot tables whenever I think I can eliminate writing formulas. I pasted in a pivot tables with what I think are the appropriate filters for your data. At least I get the same answer. Does this example solve the problem correctly?
    Norm
    Attached Files Attached Files

  16. The Following 2 Users Say Thank You to Goldennorm For This Useful Post:

    RetiredGeek (2016-03-17),zeddy (2016-03-17)

  17. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Goldennorm

    ..an excellent contribution! In the attached file, I shifted your pivot table to the top to show it closer to the other results. I like Maud's SUMPRODUCT formula that doesn't require the helper column.

    As a test, I changed the previous zero value in cell [A7] to a text value, to see what happens.
    As you can see, some of the results are more 'robust' than others. My current vote for simplicity is for RG's, and I like rory's best for the brain-stretch.

    clip1.GIF

    zeddy
    Attached Files Attached Files

  18. The Following User Says Thank You to zeddy For This Useful Post:

    Goldennorm (2016-03-17)

  19. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Matthew,

    Nice!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  20. #12
    New Lounger
    Join Date
    Feb 2010
    Location
    East Brunswick, NJ
    Posts
    15
    Thanks
    1
    Thanked 3 Times in 2 Posts
    Sorry about that placement - I just realized it this morning before reading your post.
    Norm

  21. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Norm

    ..no apologies required in this Forum.

    I'm glad that my effort to create and post some sample records has helped to get some different answers. It is always so much easier when the poster includes a sample file to work with.

    zeddy

  22. #14
    New Lounger
    Join Date
    Feb 2010
    Location
    East Brunswick, NJ
    Posts
    15
    Thanks
    1
    Thanked 3 Times in 2 Posts
    Is it "cheating"if I replace the helper column in my pivot table with a computed field that does the same thing as in this example? Actually, aside from the intellectual challenge, I prefer having the helper column included with the data set where it's more obvious what is going on. I envision scenarios where the total amount becomes important for sorting and slicing.
    Norm
    Attached Files Attached Files

  23. #15
    New Lounger
    Join Date
    Mar 2016
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Wow

    I'm overwhelmed by all the help! Thank you so much. I have a lot to go over and learn! always exciting to mater new techniques.

    Cheers,

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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