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