Forum Discussion
Solved
Need help with a formula to sum values in a range that DON'T meet all of a set of multiple criteria
Device/OS System - Desktop PC - System Type 64bit OS x64based processor
Windows 10 Pro, version 22H2, installed 7/6/2022, OS Build 19045.3803
Excel Version - Installed as Part of Microsoft 365 - en - us, 16.0.17029.20108 on 12/14/2023
I am hopelessly stuck on something in Excel.
I know how to use SUMIF (or another SUM function) to sum the values in a range that meet all of a set of multiple criteria that you specify in excel, but I don't know how to do the reverse - use SUMIF (or another SUM function) to sum the values in a range that DON'T meet all of a set of multiple criteria that you specify in excel.
Example:
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". I just want to sum any outlyers that are not labeled as any of these text values. I am trying to use relative and absolute references so that the same formula can be applied to range R$1:R$10 and range S$1:S$10, with the same criteria.
Q R S
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
If it worked properly, it would sum lines R1:R10, except the number on line 7 labeled "SpeedPay", and ignore lines R11:R12.
Then, if you copied the sum formula over to sum lines S1:S10, except the number on line 7 labeled "SpeedPay", and ignore lines R11:R12.
I wanted to attach a sample excel file, but I have not figured out how to do that.
| 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...
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.
