Forum Discussion
Need help with a formula to sum values in a range that DON'T meet all of a set of multiple criteria
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...
