SUMIFS: array for the exclude criteria

[danhendo888]

I'm wanting to Sum the Amount column using four criteria (Blue cells) in Excel (as opposed to pivot tables or PQ)

For the exclude criteria, I want to exclude Oil, Gear from column D (red cells) so that I'm only adding up the Green cells (cells colored just for visual reference)

Is it possible to use an array (like the helper column) as the exclude criteria?

Book1
	A	B	C	D	E	F	G
1	Customer	Cost_Center	Model	Part	Month	Revenue/Expense	Amount
2	Ford		Raptor	Exhaust	1	R	-572.2
3	Ford		Raptor	Windows	1	R	11.55
4	Ford		Raptor	Tyre	1	R	-4259.42
5	Ford		Raptor	Oil	1	R	78.65
6	Ford		Raptor	Gear	1	R	471.82
7
8
9
10			Sum: excluding Oil, Gear	-4820.07
11						Helper:
12						Oil
13						Gear
Sheet1

Cell Formulas
Range	Formula
D10	D10	=SUMIFS(G2:G6,A2:A6,"Ford",C2:C6,"Raptor",F2:F6,"R",D2:D6,"<>"&F12,D2:D6,"<>"&F13)

 

[awoohaw]

try this:

=SUM(IFERROR((G2:G6)/(D2:D6<>"Oil")/(D2:D6<>"Gear"),0))

or if you need the other columns assessed as well:

=SUM(IFERROR((G2:G6)*(A2:A6="Ford")*(C2:C6="Raptor")*(F2:F6="R")/(D2:D6<>"Oil")/(D2:D6<>"Gear"),0))

also here is a sumifs:

=SUMIFS(G2:G6,A2:A6,"Ford",C2:C6,"Raptor",F2:F6,"R",D2:D6,"<>Oil",D2:D6,"<>Gear")

 

[Cubist]

Two options: Exclude list or include list.

Book1
	A	B	C	D	E	F	G
1	Customer	Cost_Center	Model	Part	Month	Revenue/Expense	Amount
2	Ford		Raptor	Exhaust	1	R	-572.2
3	Ford		Raptor	Windows	1	R	11.55
4	Ford		Raptor	Tyre	1	R	-4259.42
5	Ford		Raptor	Oil	1	R	78.65
6	Ford		Raptor	Gear	1	R	471.82
7
8
9
10			Sum: excluding Oil, Gear	-4820.07	-4820.07	-4820.07
11					Exclude Helper:	Include Helper
12					Oil	Exhaust
13					Gear	Windows
14						Tyre
Sheet8

Cell Formulas
Range	Formula
D10	D10	=SUMIFS(G2:G6,A2:A6,"Ford",C2:C6,"Raptor",F2:F6,"R",D2:D6,"<>"&E12,D2:D6,"<>"&E13)
E10	E10	=SUM(G2:G6)-SUM(SUMIFS(G2:G6,A2:A6,"Ford",C2:C6,"Raptor",D2:D6,E12:E13,F2:F6,"R"))
F10	F10	=SUM(SUMIFS(G2:G6,A2:A6,"Ford",C2:C6,"Raptor",D2:D6,F12:F14,F2:F6,"R"))

 

[Georgiboy]

Should also be able to make use of SUMPRODUCT:

Book1
	A	B	C	D	E	F	G
1	Customer	Cost_Center	Model	Part	Month	Revenue/Expense	Amount
2	Ford		Raptor	Exhaust	1	R	-572.2
3	Ford		Raptor	Windows	1	R	11.55
4	Ford		Raptor	Tyre	1	R	-4259.42
5	Ford		Raptor	Oil	1	R	78.65
6	Ford		Raptor	Gear	1	R	471.82
7
8
9
10			Sum: excluding Oil, Gear	-4820.07
11						Helper:
12						Oil
13						Gear
Sheet1

Cell Formulas
Range	Formula
D10	D10	=SUMPRODUCT(--(A2:A6="Ford"),--(C2:C6="Raptor"),--(F2:F6="R"),--NOT(COUNTIF(F12:F13,D2:D6)),G2:G6)