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

1. ## 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?

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

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:

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

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

6. 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"}))

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

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

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

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

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

zeddy (2016-03-17)

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

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

zeddy (2016-03-17)

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

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

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

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

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

Goldennorm (2016-03-17)

19. Matthew,

Nice!

Norm

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

23. ## 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 Last