Need help with a formula to sum values in a range that DON'T meet all of a set of multiple criteria

[Tamara Duvall]

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

[#2 · Accepted solution]

@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]

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.

[#4]

That helped me so much! I can't thank you enough.