Forum Discussion
Need help with a formula to sum values in a range that DON'T meet all of a set of multiple criteria
| A-P | Q | R | S | T | |||||
1 | Bad Debt | 10 | 5 | ||||||
| 2 | Bad Debt | 5 | 15 | ||||||
| 3 | Bus Check | 5 | 20 | ||||||
| 4 | Check | 15 | 5 | ||||||
| 5 | PayPal | 20 | 10 | ||||||
| 6 | Check | 30 | 25 | ||||||
| 7 | SpeedPay | 35 | 10 | ||||||
| 8 | Venmo | 25 | 5 | ||||||
| 9 | Check | 15 | 40 | ||||||
| 10 | Check | 20 | 35 | ||||||
| 11 | Bus Check | 14 | 7 | ||||||
| 12 | Bus Check | 21 | 14 | ||||||
| 13 | |||||||||
| 14 | TOTALS | 145 | 160 | <--- desired totals to receive if formula is working properly | |||||
Tamara Duvall There's some conflicting information in your explanation:
I have a list of numbers in the range R$1:R$10 that I want to sum as long as they AREN'T labeled in the range $Q$1:$Q$10 with text values equal to "Bad Debt","Bus Check","Check","PayPal", AND "Venmo"....If it worked properly, it would sum lines R1:R10, except the number on line 7 labeled "SpeedPay".These two statements contradict one another. If they AREN'T labeled "Bad Debt", "Bus Check", "Check", "PayPal" and "Venmo", then the total would sum ONLY "SpeedPay", which would be 35.
Having said that, here's a couple of examples that might be what you're looking for...
To exclude all items in the list:
=SUM(FILTER(R$1:R$10, ISNA(XMATCH($Q$1:$Q$10, {"Bad Debt";"Bus Check";"Check";"PayPal";"Venmo"})), 0))To include all items in the list:
=SUM(SUMIFS(R$1:R$10, $Q$1:$Q$10, {"Bad Debt";"Bus Check";"Check";"PayPal";"Venmo"}))Adjust the range references as required. Please see the attached workbook for more examples...
3 Replies
Tamara Duvall There's some conflicting information in your explanation:
I have a list of numbers in the range R$1:R$10 that I want to sum as long as they AREN'T labeled in the range $Q$1:$Q$10 with text values equal to "Bad Debt","Bus Check","Check","PayPal", AND "Venmo"....If it worked properly, it would sum lines R1:R10, except the number on line 7 labeled "SpeedPay".These two statements contradict one another. If they AREN'T labeled "Bad Debt", "Bus Check", "Check", "PayPal" and "Venmo", then the total would sum ONLY "SpeedPay", which would be 35.
Having said that, here's a couple of examples that might be what you're looking for...
To exclude all items in the list:
=SUM(FILTER(R$1:R$10, ISNA(XMATCH($Q$1:$Q$10, {"Bad Debt";"Bus Check";"Check";"PayPal";"Venmo"})), 0))To include all items in the list:
=SUM(SUMIFS(R$1:R$10, $Q$1:$Q$10, {"Bad Debt";"Bus Check";"Check";"PayPal";"Venmo"}))Adjust the range references as required. Please see the attached workbook for more examples...
- That helped me so much! I can't thank you enough.
- Thank you so much for figuring out what I was trying to say. I have been very sleep deprived the last few days. I will give this a try in the AM.
