Sumif if range does not equal any text in a range

[Luigi802]

I have an expense report, and I want to sum the amount that does not equal a range of text, i.e.

Description Amount Category
misc.item $20.32 Gas
misc.item $10.20 Food
misc.item $5.68 Snacks
misc.item $40.00 Gas
misc.item $6.48 Food
misc.item $32.78 Gas
misc.item $4.32 Vending
misc.item $100.32 Food
misc.item $4.50 Lunch
misc.item $42.50 Gas
misc.item $23.50 Gift

How do I sum everything that IS NOT gas or food?
Also I will probably add a lot of other misc. things so =sumifs(B2:B12,C1:C12,"<>Gas",C1:C12,"<>Food") won't work for me cause I'd have to add another criteria every time something new is added

[FlameRetired]

Try

Please Login or Register  to view this content.

[Luigi802]

What if that range of text was in a named list (different sheet), for example, I have a list called "Not_L" list it contains 6 different text items, but when I try to plug that into your formula =SUM(Table9[Average])-SUM(SUMIFS(Table9[Average],Table9[Expense],Not_L)) it gives me an error, and if I put the Not_L in "" it just totals everything.

[FlameRetired]

Welcome back.

Wow. That's been a little more than 3 years ago. I'll see what I can come up with.

[Luigi802]

Welcome back.

Wow. That's been a little more than 3 years ago. I'll see what I can come up with.

Yes it has been awhile, thank you

[FlameRetired]

Please post a sample workbook that is a representative example of what you are trying to do ... and please do not post a picture or screenshot.

The instructions for doing that are in the 'gold' banner at the top of this page.

Edit In the meantime try entering your formula as an array formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[Luigi802]

Here is a sample sheet, in B3 is where I want to sum everything in the table that is NOT in the "NOT_L" list

[FlameRetired]

The syntax needs to be changed to this

Please Login or Register  to view this content.

and then yes it must be committed as an array formula.

Edit Never mind. It works fine.

[Luigi802]

The syntax needs to be changed to this

Please Login or Register  to view this content.

and then yes it must be committed as an array formula.

Edit Never mind. It works fine.

Well it came up with a number, but not the correct number

[FlameRetired]

I am clearly missing something. I have to step out for a few hours.

In the meantime it would be best if you unmark your thread as Solved to Unsolved. More participants will likely look at it then.

[CARACALLA]

	B	C	D	E	F	G
4						sum without NOT_L
5	Expense	Average		Not_L		268
6	Amazon	$1.00		Interest
7	Apparel	$2.00		Other Income
8	ATM	$3.00		Pay
9	Automotive Expenses	$4.00		Refunds/Adjustments
10	Bills	$5.00		Rewards
11	Dad (Quintana)	$6.00		Transfers
12	Dollar Store	$7.00
13	Gifts	$8.00
14	Groceries	$9.00
15	Household	$10.00
16	Interest	$11.00
17	Lily	$12.00
18	N/A	$13.00
19	NSF	$14.00
20	Other Income	$15.00
21	Pay	$16.00
22	PayPal	$17.00
23	Personal Care	$18.00
24	Pet Care	$19.00
25	Qapital	$20.00
26	Refunds/Adjustments	$21.00
27	Rewards	$22.00
28	Security	$23.00
29	Tithe	$24.00
30	Transfers	$25.00
31	Travel	$26.00
32	Walmart	$27.00

G5=SUMPRODUCT((ISNA(MATCH(Table1[Expense],Table2[Not_L],0)))*(Table1[Average]))

[FlameRetired]

Another method ... piggy-backing on CARACALLA's data.

Please Login or Register  to view this content.

	B	C	D	E	F	G
5	Expense	Average		Not_L		$268.00
6	Amazon	$1.00		Interest
7	Apparel	$2.00		Other Income
8	ATM	$3.00		Pay
9	Automotive Expenses	$4.00		Refunds/Adjustments
10	Bills	$5.00		Rewards
11	Dad (Quintana)	$6.00		Transfers
12	Dollar Store	$7.00
13	Gifts	$8.00
14	Groceries	$9.00
15	Household	$10.00
16	Interest	$11.00
17	Lily	$12.00
18	N/A	$13.00
19	NSF	$14.00
20	Other Income	$15.00
21	Pay	$16.00
22	PayPal	$17.00
23	Personal Care	$18.00
24	Pet Care	$19.00
25	Qapital	$20.00
26	Refunds/Adjustments	$21.00
27	Rewards	$22.00
28	Security	$23.00
29	Tithe	$24.00
30	Transfers	$25.00
31	Travel	$26.00
32	Walmart	$27.00

[Luigi802]

Another method ... piggy-backing on CARACALLA's data.

Please Login or Register  to view this content.

	B	C	D	E	F	G
5	Expense	Average		Not_L		$268.00
6	Amazon	$1.00		Interest
7	Apparel	$2.00		Other Income
8	ATM	$3.00		Pay
9	Automotive Expenses	$4.00		Refunds/Adjustments
10	Bills	$5.00		Rewards
11	Dad (Quintana)	$6.00		Transfers
12	Dollar Store	$7.00
13	Gifts	$8.00
14	Groceries	$9.00
15	Household	$10.00
16	Interest	$11.00
17	Lily	$12.00
18	N/A	$13.00
19	NSF	$14.00
20	Other Income	$15.00
21	Pay	$16.00
22	PayPal	$17.00
23	Personal Care	$18.00
24	Pet Care	$19.00
25	Qapital	$20.00
26	Refunds/Adjustments	$21.00
27	Rewards	$22.00
28	Security	$23.00
29	Tithe	$24.00
30	Transfers	$25.00
31	Travel	$26.00
32	Walmart	$27.00

AWESOME! That Worked! THANKS AGAIN!!

[Luigi802]

	B	C	D	E	F	G
4						sum without NOT_L
5	Expense	Average		Not_L		268
6	Amazon	$1.00		Interest
7	Apparel	$2.00		Other Income
8	ATM	$3.00		Pay
9	Automotive Expenses	$4.00		Refunds/Adjustments
10	Bills	$5.00		Rewards
11	Dad (Quintana)	$6.00		Transfers
12	Dollar Store	$7.00
13	Gifts	$8.00
14	Groceries	$9.00
15	Household	$10.00
16	Interest	$11.00
17	Lily	$12.00
18	N/A	$13.00
19	NSF	$14.00
20	Other Income	$15.00
21	Pay	$16.00
22	PayPal	$17.00
23	Personal Care	$18.00
24	Pet Care	$19.00
25	Qapital	$20.00
26	Refunds/Adjustments	$21.00
27	Rewards	$22.00
28	Security	$23.00
29	Tithe	$24.00
30	Transfers	$25.00
31	Travel	$26.00
32	Walmart	$27.00

G5=SUMPRODUCT((ISNA(MATCH(Table1[Expense],Table2[Not_L],0)))*(Table1[Average]))

Actually this 1 worked! Thanks you!