Countifs & sumproduct with multiple curly bracket not calculating correctly

[fayez_MrExcel]

I have a table where i want to count records with multiple criteria having multiple curly brackets, but it seems it is not calculating correctly. In below table, im expecting the result count as 4, but the countifs is giving me 1 only and sumproduct is returning #N/A

CopyofTerminatedEmployments-Component1 (4).csv
	A	B	C	D	E	F	G	H
1
2		Type	Item	Cart		Formula Used	Result
3		Office	Pen	Box 1		COUNTIFS	1
4		Kitchen	Fork	Box 2		SUMPRODUCT	#N/A
5		Office	Monitor	Box 1
6		Garden	Pot	Box 1
7		Kitchen	Knife	Box 2
8		Office	Monitor	Box 3
9		Kitchen	Knife	Box 1
10		Kitchen	Spoon	Box 3
11		Office	Pencil	Box 2
12		Kitchen	Plate	Box 1
13		Garden	Shovel	Box2
14
15
Sheet3

Cell Formulas
Range	Formula
G3	G3	=SUM(COUNTIFS($B$3:$B$13,{"Office","Kitchen"},$C$3:$C$13,{"Pen","Monitor","Knife"},$D$3:$D$13,{"Box 1","Box 2"}))
G4	G4	=SUMPRODUCT(($B$3:$B$13={"Office","Kitchen"})*($C$3:$C$13={"Pen","Monitor","Knife"})*($D$3:$D$13={"Box 1","Box 2"}))

 

[Peter_SSs]

Unfortunately you cannot use syntax like that in those functions. What about this?

22 10 31.xlsm
	A	B	C	D	E		G
1
2		Type	Item	Cart			Result
3		Office	Pen	Box 1			4
4		Kitchen	Fork	Box 2
5		Office	Monitor	Box 1
6		Garden	Pot	Box 1
7		Kitchen	Knife	Box 2
8		Office	Monitor	Box 3
9		Kitchen	Knife	Box 1
10		Kitchen	Spoon	Box 3
11		Office	Pencil	Box 2
12		Kitchen	Plate	Box 1
13		Garden	Shovel	Box 2
Count

Cell Formulas
Range	Formula
G3	G3	=SUM(((B3:B13="Office")+(B3:B13="Kitchen"))*((C3:C13="Pen")+(C3:C13="Monitor")+(C3:C13="Knife"))*((D3:D13="Box 1")+(D3:D13="Box 2")))

 

[Dave Patton]

Alternative 1 The data is in an Excel Table
Alternative 2 Sumproduct

T202210a.xlsm
	A	B	C	D	E	F	G
1
2		Count		4
3
4		Type	Item	Cart
5		Office	Pen	Box 1
7		Office	Monitor	Box 1
9		Kitchen	Knife	Box 2
11		Kitchen	Knife	Box 1
16
17
18
19
20
21		Type	Item	Cart		Formula Used	Result
22		Office	Pen	Box 1
23		Kitchen	Fork	Box 2		SUMPRODUCT	4
24		Office	Monitor	Box 1
25		Garden	Pot	Box 1
26		Kitchen	Knife	Box 2
27		Office	Monitor	Box 3
28		Kitchen	Knife	Box 1
29		Kitchen	Spoon	Box 3
30		Office	Pencil	Box 2
31		Kitchen	Plate	Box 1
32		Garden	Shovel	Box2
33
5d

Cell Formulas
Range	Formula
D2	D2	=SUBTOTAL(103,Table2[Cart])
G23	G23	=SUMPRODUCT(--(ISNUMBER(MATCH($B$22:$B$32,{"Office","Kitchen"},0))),--(ISNUMBER(MATCH($C$22:$C$32,{"Pen","Monitor","Knife"},0))),--(ISNUMBER(MATCH($D$22:$D$32,{"Box 1","Box 2"},0))))