Count how many of the last 5, by person

[ladylissa21]

Hello out there! I've been using a formula to count how many of the last 5 items were considered proficient. The problem is that because there are a different number of items for each person, and multiple persons, I have to manually tweak each formula to only count the reviews for that person. For example, using the data below, right now I am using this formula to review Sam's work: =COUNTIF((OFFSET($C$2,COUNTA(C1:C8)-MIN(COUNTA(C1:C8),6),0,MIN(COUNTA(C1:C8),6),1)),"*Proficient*"). And I am using this formula to review Melissa's work: =COUNTIF((OFFSET($C$2,COUNTA(C9:C14)-MIN(COUNTA(C9:C14),6),0,MIN(COUNTA(C9:C14),6),1)),"*Proficient*"). Is there a way to incorporate something in this formula to check all of C:C, and just report how many of the last 5 were proficient for each person?

	A</SPAN>	</SPAN>B	</SPAN>C	D
	Worker</SPAN>	Date Worked</SPAN>	Result</SPAN>	Proficient in Last 5</SPAN>
2</SPAN>	Sam</SPAN>	3/1/2015</SPAN>	Proficient</SPAN>	</SPAN> 2​
3</SPAN>	Sam</SPAN>	3/3/2015</SPAN>	Proficient</SPAN>
4</SPAN>	Sam</SPAN>	3/7/2015</SPAN>	Intermediate</SPAN>
5</SPAN>	Sam</SPAN>	3/15/2015</SPAN>	Proficient</SPAN>
6</SPAN>	Sam</SPAN>	3/16/2015</SPAN>	Intermediate</SPAN>
7</SPAN>	Sam</SPAN>	3/20/2015</SPAN>	Intermediate</SPAN>
8</SPAN>	Sam</SPAN>	3/22/2015</SPAN>	Proficient</SPAN>
9</SPAN>	Melissa</SPAN>	3/2/2015</SPAN>	Intermediate</SPAN>	3</SPAN>​
10</SPAN>	Melissa</SPAN>	3/5/2015</SPAN>	Intermediate</SPAN>
11</SPAN>	Melissa</SPAN>	3/7/2015</SPAN>	Proficient</SPAN>
12</SPAN>	Melissa</SPAN>	3/12/2015</SPAN>	Proficient</SPAN>
13</SPAN>	Melissa</SPAN>	3/18/2015</SPAN>	Intermediate</SPAN>
14</SPAN>	Melissa</SPAN>	3/19/2015</SPAN>	Proficient</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>

 

[RonB1111]

If there are always at least 5 rows for each person, put your 1st row of data in row 5 (instead of 2) and enter this in D5 and copy down:
=IF(A5=A6,"",COUNTIF(C1:C5,"Proficient"))

 

[ladylissa21]

If there are always at least 5 rows for each person, put your 1st row of data in row 5 (instead of 2) and enter this in D5 and copy down:
=IF(A5=A6,"",COUNTIF(C1:C5,"Proficient"))

Thank you for replying! Won't that count all of the "proficient" statuses for that person though? I need it to look at only the last 5 entries, too. In my example there are 7 entries for Sam - the oldest two can't be a factor in the count.

 

[RonB1111]

Did you try the formula as described? Please verify Sam's data is in rows 5 thru 11 and Melissa's is in 12 thru 17. Also please verify you've copied my formula into D5 and then copied it down from there. In cell D11 my result is 2 for Sam and in D17 the result for Melissa is 3, which I believe are the answers you're expecting. The formula does only count the last 5 records if everything is setup as I've described.