SUMIFS IGNORE LIST CRITERIA

[JJLEFROGGE]

Dummy data - attached

I have 3 input variables (using data validation lists). Each list has the word "All" inserted. However, the word "All" does not appear in my data -- I am using "All" as a way of selecting all records.

I want to create a sumifs formula that will calculate total spend when "All" is selected as an input variable(s).

 

[Toadstool]

Hi JJLeFrogge,

Just replace "All" with the wildcard option "*".

JJLeFrogge.xlsx
	B	C	D	E	F	G	H	I
1	Selectors			Spend		Fruit	Vegetables	Meat
2	Fruit	Apples		5900		*	*	*
3	Vegetables	Carrots				Apples	Carrots	Chicken
4	Meat	*				Pears	Onions	Beef
5						Oranges	Potatoes	Lamb
6						Lemons
11
12	ID	Fruit	Vegetables	Meat	Spend
13	1	Apples	Carrots	Chicken	1000
14	2	Pears	Carrots	Beef	2000
15	3	Apples	Carrots	Lamb	4000
16	4	Oranges	Carrots	Chicken	6000
17	5	Apples	Onions	Beef	7000
18	6	Oranges	Potatoes	Lamb	8000
19	7	Apples	Carrots	Chicken	900
20	8	Oranges	Carrots	Chicken	12000
21	9	Pears	Onions	Beef	14000
22	10	Pears	Potatoes	Lamb	16000
Master

Cell Formulas
Range	Formula
E2	E2	=SUMIFS($F$13:$F$22,$C$13:$C$22,$C$2,$D$13:$D$22,$C$3,$E$13:$E$22,$C$4)

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$G$2:$G$6
C3	List	=$H$2:$H$5
C4	List	=$I$2:$I$5

 

[JJLEFROGGE]

Toadstool, it seems so appropriate that you should be helping a Frog. (my nickname!)

Thank you - such a simple solution. Thank you so much - I really appreciate your help.

 

[Toadstool]

You're welcome!