Sumproduct with multiple criteria, month and year

[Danzrox]

Hello. I would appreciate some help.
What I am trying to do is to calculate the sum of the values that have the criteria needed.

Example:

As you can see the answer is 4 but it should be 1 (only for door, open, in jan-19).

 

[AhoyNC]

Here is one way. Notice that I put Open and Door into separate columns.

Book1
	A	B	C	D	E	F	G
1	item	date	status			Open	Door
2	Door	1/1/2019	open		1/1/2019	1
3	Cab	4/5/2019	closed
4	Drawer	2/7/2019	open
5	Door	5/1/2019	closed
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(--($A$2:$A$5=$G$1),--($C$2:$C$5=$F$1),--(MONTH($B$2:$B$5)=MONTH($E$2)),--(YEAR($B$2:$B$5)=YEAR($E$2)))

 

[ER_Neha]

Hello I tried to solve it. I am stuck while incorporating the date criteria, I hope someone will help customizing it too. Rather than that I posting the formula for criteria- door & open- I hope it help.

Book1
	A	B	C	D	E	F	G
1	item	date	status		CRITERIA 1	CRITERIA 2	CALCULATED NO.
2	Door	01-01-2019	open		Door	Open	1
3	Cabinet	04-05-2019	closed
4	Drawer	02-07-2019	open
5	Door	05-01-2019	closed
6
Sheet1

Cell Formulas
Range	Formula
G2	G2	=COUNTIFS($A$2:$A$5,$E$2,$C$2:$C$5,$F$2)

 

[Danzrox]

Here is one way. Notice that I put Open and Door into separate columns.

Book1
	A	B	C	D	E	F	G
1	item	date	status			Open	Door
2	Door	1/1/2019	open		1/1/2019	1
3	Cab	4/5/2019	closed
4	Drawer	2/7/2019	open
5	Door	5/1/2019	closed
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(--($A$2:$A$5=$G$1),--($C$2:$C$5=$F$1),--(MONTH($B$2:$B$5)=MONTH($E$2)),--(YEAR($B$2:$B$5)=YEAR($E$2)))

Thanks Ahoy but it ended up looking at only open cells at 01/01/19. I need it to look up every open cell in the month of jan-19.

 

[ER_Neha]

Thanks Ahoy but it ended up looking at only open cells at 01/01/19. I need it to look up every open cell in the month of jan-19.

Will you like to have a helper column ?

 

[Danzrox]

Will you like to have a helper column ?

I'm not sure ER_Neha. Column E should be Jan-19, Feb-19, Mar-19 OR could be 1, 2, 3, 4.
It is a spreadsheet with 4000 rows

 

[ER_Neha]

I'm not sure ER_Neha. Column E should be Jan-19, Feb-19, Mar-19 OR could be 1, 2, 3, 4.
It is a spreadsheet with 4000 rows

Hello I used a Helper column and I have made the calculation dynamic according to the criteria kindly have a look and please reply if it worked for you

Book1
	A	B	C	D	E	F	G	H	I
1	item	date	status	Helper Column		CRITERIA 1	CRITERIA 2	CRITERIA 3	CALCULATED NO.
2	Door	01-01-2019	open	Jan-19		Door	Open	Jan-19	1
3	Cabinet	04-05-2019	closed	May-19
4	Drawer	02-07-2019	open	Jul-19
5	Door	05-01-2019	closed	Jan-19
6
Sheet1

Cell Formulas
Range	Formula
I2	I2	=COUNTIFS($A$2:$A$5,$F$2,$C$2:$C$5,$G$2,$D$2:$D$5,$H$2)
D2:D5	D2	=TEXT(MONTH(B2)&"-"&YEAR(B2),"mmm-yy")

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=$D$2:$D$5

 

[AhoyNC]

Danzrox - in your example there is only 1 open door in Jan. You will need to change the ranges in my formula to match your data.
Er_Neha - The example below also shows using COUNTIFS with a date range. Note: my dates are formatted as "m/d/y". The date in cell F2 was entered as 1/1/19 and then formatted as "mmm-yy".

Book1
	A	B	C	D	E	F	G
1	item	date	status			Open	Door
2	Door	1/1/2019	open		1/1/2019	1
3	Cab	5/4/2019	closed			1
4	Drawer	7/2/2019	open
5	Door	1/5/2019	closed
Sheet2

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(--($A$2:$A$5=$G$1),--($C$2:$C$5=$F$1),--(MONTH($B$9:$B$12)=MONTH($E$2)),--(YEAR($B$9:$B$12)=YEAR($E$2)))
F3	F3	=COUNTIFS($A$2:$A$5,$G$1,$C$2:$C$5,$F$1,$B$2:$B$5,">="&$E$2,$B$2:$B$5,"<="&EOMONTH($E$2,0))