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?)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Test Date[/TD]
[TD]Student[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]7/28/19[/TD]
[TD]Sara J[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]6/20/19[/TD]
[TD]Sara J[/TD]
[TD]233[/TD]
[/TR]
[TR]
[TD]3/15/19[/TD]
[TD]Alphie D[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]6/28/19[/TD]
[TD]Barney C[/TD]
[TD]P[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Test Date	Student	Score
Sara J	P
Sara J
Alphie D
Barney C	P

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]28-7-2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]20-6-2019[/TD]

[TD="align: right"]233[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]15-3-2019[/TD]

[TD="align: right"]230[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]28-6-2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=COUNTIFS(C2:C5,"P",A2:A5,">="&DATE(2019,3,1),A2:A5,"<="&DATE(2019,5,15))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Test	START DATE
Sara J	END DATE
Sara J
Alphie D
Barney C
Test

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]TEST DATE[/TD]
[TD="align: center"]STUDENT[/TD]
[TD="align: center"]SCORE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]01-Mar-19[/TD]

[TD="align: center"]P[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]01-Mar-19[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]28-Jul-19[/TD]

[TD="align: center"]P[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]15-May-19[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]20-Jun-19[/TD]

[TD="align: center"]233[/TD]
[TD="align: right"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]15-Mar-19[/TD]

[TD="align: center"]230[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]15-May-19[/TD]

[TD="align: center"]P[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]28-Jun-19[/TD]

[TD="align: center"]150[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]F4[/TH]
[TD="align: left"]=COUNTIFS($C$2:$C$10,$E$4,$A$2:$A$10,">="&$F$2,$A$2:$A$10,"<="&$F$3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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)


[TABLE="class: grid, width: 500"]
<tbody style="border-collapse: collapse; width: auto;">[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column f[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Student[/TD]
[TD]Math[/TD]
[TD]English[/TD]
[TD]History[/TD]
[TD]Start measurement period[/TD]
[TD]End measurement period[/TD]
[/TR]
[TR]
[TD]3/1/19[/TD]
[TD]AV[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]220[/TD]
[TD]4/1/19[/TD]
[TD]6/30/19[/TD]
[/TR]
[TR]
[TD]3/28/19[/TD]
[TD]AV[/TD]
[TD][/TD]
[TD]232[/TD]
[TD][/TD]
[TD]7/1/19[/TD]
[TD]9/30/19[/TD]
[/TR]
[TR]
[TD]4/25/19[/TD]
[TD]Jess[/TD]
[TD][/TD]
[TD]P[/TD]
[TD]215[/TD]
[TD]10/1/19[/TD]
[TD]12/31/19[/TD]
[/TR]
[TR]
[TD]5/5/19[/TD]
[TD]Sara[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/28/19[/TD]
[TD]John[/TD]
[TD]210[/TD]
[TD]233[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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))