COUNTIF and COUNTA formula as a percentage

[Springer_30]

Good day

Have not posted here in ages but have a small problem

In the sheet below there are for example five students who have arrived either "Late" or "On Time" for classes over a period of three days

How do I calculate the percentage for each student of being "On Time" for the three days using COUNTIF and /or COUNTA?

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]Day 1
[/TD]
[TD]On Time / Late[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]% On Time[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Late[/TD]
[TD][/TD]
[TD]David[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Simon[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD]Simon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD]Kate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Late[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Simon[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Late[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Late[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Late[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Simon[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]Late[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Firstly that isnt a great way to set out your data. You would be much better off having the headers Name, Day and On Time/Late rather than just the two headers and blocks of days. That said you can use COUNTIFS. eg:

=COUNTIFS(A:A,D2,B:B,"On Time")/COUNTIFS(A:A,D2)

Format to percentage.

 

[Springer_30]

Thanks Steve, it has worked

BTW, I do fully agree that the way the data was set out isn't great at all, it was however just put together for the purpose of attaining the formula