count values between dates

[crashbrn34]

I have the below spreadsheet for test scores. The first column lists the test dates for each student and another lists the test scores. if the student passed, there's a "P" for their score. The spreadsheet has 260 records with dates throughout the year. I'd like to count how many records with a date between 3/1/19 and 5/15/19 have a "P" listed. (Is it necessary to convert the "P" to a number (e.g., 100) so all values are numerical?)

Test Date	Student	Score
7/28/19	Sara J	P
6/20/19	Sara J	233
3/15/19	Alphie D	230
6/28/19	Barney C	P

<tbody>
</tbody>

 

[jorismoerings]

Hi and welcome to MrExcel,

try this:

Book1
	A	B	C	D	E
1	Test Date	Student	Score
2	28-7-2019	Sara J	P
3	20-6-2019	Sara J	233
4	15-3-2019	Alphie D	230		0
5	28-6-2019	Barney C	P
Sheet1

Cell Formulas
Range	Formula
E4	=COUNTIFS(C2:C5,"P",A2:A5,">="&DATE(2019,3,1),A2:A5,"<="&DATE(2019,5,15))

 

[Sam_D_Ben]

Another way is,

Excel 2013/2016
	A	B	C	D	E	F
1	TEST DATE	STUDENT	SCORE
2	01-Mar-19	Test	P		START DATE	01-Mar-19
3	28-Jul-19	Sara J	P		END DATE	15-May-19
4	20-Jun-19	Sara J	233		P	2
5	15-Mar-19	Alphie D	230
6	15-May-19	Barney C	P
7	28-Jun-19	Test	150
Sheet1

Cell Formulas
Range	Formula
F4	=COUNTIFS($C$2:$C$10,$E$4,$A$2:$A$10,">="&$F$2,$A$2:$A$10,"<="&$F$3)

 

[crashbrn34]

That worked. I also used a formula where I listed the start and end dates for the period i wanted to count and referenced those--that way of calculating worked as well....=COUNTIFS(A2:A5, ">="&W10, A2:A5,"<="&X10, C2:C5, "P"), where X and W were the start and end dates.

But now I have another issue. I want to count how many times "P" is listed across multiple columns (I added scores for other tests in columns C & D). When I tried to update the formula to count "P" across the 3 columns (changing the above to C2: D5), it returned a #VALUE error.

Hi and welcome to MrExcel,

try this:

	A	B	C	D	E
1	Test Date	Student	Score
2	28-7-2019	Sara J	P
3	20-6-2019	Sara J	233
4	15-3-2019	Alphie D	230		0
5	28-6-2019	Barney C	P

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E4 =COUNTIFS(C2:C5,"P",A2:A5,">="&DATE(2019,3,1),A2:A5,"<="&DATE(2019,5,15)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[crashbrn34]

yes this is what i used... but now another issue is that i want to expand the formula to count P in scores i added to columns C and D. When I modify the range from C2:D5, I get a #VALUE error

 

[crashbrn34]

yes this is what i used... but now another issue is that i want to expand the formula to count P in scores i added to columns C and D. When I modify the range from C2:D5, I get a ​

#VALUE

error

​

Another way is,

Excel 2013/2016

	A	B	C	D	E	F
1	TEST DATE	STUDENT	SCORE
2	01-Mar-19	Test	P		START DATE	01-Mar-19
3	28-Jul-19	Sara J	P		END DATE	15-May-19
4	20-Jun-19	Sara J	233		P	2
5	15-Mar-19	Alphie D	230
6	15-May-19	Barney C	P
7	28-Jun-19	Test	150

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F4 =COUNTIFS($C$2:$C$10,$E$4,$A$2:$A$10,">="&$F$2,$A$2:$A$10,"<="&$F$3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Sam_D_Ben]

yes this is what i used... but now another issue is that i want to expand the formula to count P in scores i added to columns C and D. When I modify the range from C2:D5, I get a ​

#VALUE

error

​

I guess if you can try this.

=SUMPRODUCT(($C$2:$D$7=$E$4)*(($A$2:$A$7>=$F$2)*($A$2:A7<=$F$3)))

 

[crashbrn34]

What if I want to convert the COUNTIFS formula that calculates P for one test during a time period into an average % for the number of tests with a P during the date ranges? (see below)

Column A	Column B	Column C	Column D	Column E	Column f	Column G
Date	Student	Math	English	History	Start measurement period	End measurement period
3/1/19	AV	P		220	4/1/19	6/30/19
3/28/19	AV		232		7/1/19	9/30/19
4/25/19	Jess		P	215	10/1/19	12/31/19
5/5/19	Sara	P	P
7/28/19	John	210	233

<tbody style="border-collapse: collapse; width: auto;">
</tbody>

 

[DanteAmor]

Do you need something like this?


You just have to adjust the formula of @Sam_D_Ben in post #4


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:127.37px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:172.04px;" /><col style="width:172.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Date</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Student</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Math</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">English</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">History</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Start measurement period</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">End measurement period</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">P</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">01/mar/2019</td><td >AV</td><td >P</td><td > </td><td style="text-align:right; ">220</td><td style="background-color:#c2d69a; text-align:right; ">01/abr/2019</td><td style="background-color:#c2d69a; text-align:right; ">30/jun/2019</td><td style="background-color:#c2d69a; text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">28/mar/2019</td><td >AV</td><td > </td><td style="text-align:right; ">232</td><td > </td><td style="background-color:#93cddd; text-align:right; ">01/jul/2019</td><td style="background-color:#93cddd; text-align:right; ">30/sep/2019</td><td style="background-color:#93cddd; text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">25/abr/2019</td><td >Jess</td><td style="background-color:#c2d69a; "> </td><td style="background-color:#c2d69a; ">P</td><td style="background-color:#c2d69a; text-align:right; ">215</td><td style="background-color:#ffc000; text-align:right; ">01/oct/2019</td><td style="background-color:#ffc000; text-align:right; ">31/dic/2019</td><td style="background-color:#ffc000; text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">30/ago/2019</td><td >Sara</td><td style="background-color:#93cddd; ">P</td><td style="background-color:#93cddd; ">P</td><td style="background-color:#93cddd; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">28/jul/2019</td><td >John</td><td style="background-color:#93cddd; text-align:right; ">210</td><td style="background-color:#93cddd; text-align:right; ">233</td><td style="background-color:#93cddd; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">28/ago/2019</td><td >Sara</td><td style="background-color:#93cddd; "> </td><td style="background-color:#93cddd; "> </td><td style="background-color:#93cddd; ">P</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">28/sep/2019</td><td >Dam</td><td style="background-color:#93cddd; ">P</td><td style="background-color:#93cddd; "> </td><td style="background-color:#93cddd; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">28/oct/2019</td><td >Sue</td><td style="background-color:#ffc000; "> </td><td style="background-color:#ffc000; "> </td><td style="background-color:#ffc000; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">28/nov/2019</td><td >James</td><td style="background-color:#ffc000; "> </td><td style="background-color:#ffc000; ">P</td><td style="background-color:#ffc000; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">28/dic/2019</td><td >Rob</td><td style="background-color:#ffc000; "> </td><td style="background-color:#ffc000; "> </td><td style="background-color:#ffc000; ">P</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=SUMPRODUCT(($A$2:$A$11>=F2)*($A$2:$A$11<=G2)*($C$2:$E$11="P"))</td></tr></table></td></tr></table>

 

[Sam_D_Ben]

Do you need something like this?


You just have to adjust the formula of @Sam_D_Ben in post #4

Hi DanteAmor, Thanks a lot for pointing it. I was stuck with that part.

The OP wants the average count of "P" in % with in the given period.

I guess so.

Excel 2013/2016
	H	I	J	K	L	M	N	O	P
1	DATE	STUDENT	MATH	ENGLISH	HISTORY	START MEASUREMENT PERIOD	END MEASUREMENT PERIOD	SELECT SUBJECT	AVG%
2	3/1/2019	AV	P		220	4/1/2019	6/30/2019	MATH	33%
3	3/28/2019	AV		232		7/1/2019	9/30/2019	COUNT P
4	4/25/2019	Jess		P	215	10/1/2019	12/31/2019	P
5	5/5/2019	Sara	P	P
6	7/28/2019	John	210	233
31Aug18

Cell Formulas
Range	Formula
P2	=SUMPRODUCT(($J$1:$L$1=$O$2)*($H$2:$H$6>=$M$2)*($H$2:$H$6<=$N$2)*($J$2:$L$6=$O$4))/SUMPRODUCT(($H$2:$H$6>=$M$2)*($H$2:$H$6<=$N$2)*($J$2:$L$6=$O$4))