Count where the condition1 is a specific value and where condition2 is a date range

[standardtoaster]

Hello!

I'm trying to count all P1, P2 and P3 problems opened in January 2012, in Sheet1.
All of my problems are listed in Sheet2.

My challenge is Sheet2 contains problems opened on other dates, and I can't figure out how to count where the condition1 is a specific value and where condition2 is a date range.

This is how I have been thinking about it...

If Sheet2!B:B equals "P1" and Sheet2!C:C equals a date in January 2012
 
     Then count, and store value in Sheet1!B3

Excel Workbook
	C	D	E
1	Problems by Priority
2	Priority	January	YTD
3	P1 - Critical
4	P2 - High
5	P3 - Average
Sheet1

Excel 2003

Excel Workbook
	A	B	C
1	PROBLEMS: ROTATING LIST
2	Problem ID	Priority	Open Time
3	PM00000310	P3	Wed 12/21/11
4	PM00000116	P2	Thu 9/8/11
5	PM00000092	P2	Mon 8/22/11
6	PM00000321	P2	Thu 1/5/12
7	PM00000350	P2	Mon 1/30/12
8	PM00000223	P2	Wed 11/2/11
9	PM00000344	P2	Wed 1/25/12
10	PM00000348	P1	Fri 1/27/12
11	PM00000277	P1	Thu 12/1/11
12	PM00000346	P1	Wed 1/25/12
13	PM00000119	P2	Tue 9/13/11
14	PM00000322	P3	Fri 1/6/12
15	PM00000307	P2	Mon 12/19/11
16	PM00000296	P2	Mon 12/12/11
17	PM00000312	P2	Thu 12/22/11
18	PM00000161	P2	Mon 10/3/11
19	PM00000347	P1	Wed 1/25/12
Sheet2

Excel 2003

 

[J.Ty.]

Try

=SUMPRODUCT(($C$B:$B$19="P1")*(MONTH($C$3:$C$19)=1)*(YEAR($C$3:$C$19)=2012))

and let me know, whether it is waht you wanted to have. Of course you should modify the ranges to fit your data location.

Best,

J.Ty.

 

[J.Ty.]

Try

=SUMPRODUCT(($C$B:$B$19="P1")*(MONTH($C$3:$C$19)=1)*(YEAR($C$3:$C$19)=2012))

and let me know, whether it is what you wanted to have. Of course you should modify the ranges to fit your data location.

Best,

J.Ty.