Excel: counting many occurrences as half an occurrence (.5)

[cn117279]

Hey guys!

I am new to MrExcel and I have a confusing question for you all.

I am working on an attendance tracker for work and we have a unique policy that for every 4 occurrences someone is late less then 25 minutes (tardy) they are given .5 a point towards their record I use "T" to identify it on the tracker.

So basically if they were tardy 3 three times, 0 points
tardy 4 times: .5 points
tardy 7 times still .5 points
tardy 8 times they would get one point​

I also needed to track if they were late over 25 minutes which I use "L" to identify. After a confusing couple of minutes I figured out a formula that works:

=SUMPRODUCT(COUNTIF($O11:$AS11,$K$8),{0.5})

(Cell K8 has the letter "L" in it)

I tired manipulating that formula to get to the other 4 occurrences formula but ended up more confused then when I started.


Anyways! I am still not able to figure out a formula for the '4 occurrences of T' equal to half a point (.5). Any help would be greatly appreciated!

 

[Scott Huish]

The SUMPRODUCT isn't necessary.

Here's your Tardy formula:

=ROUNDDOWN((COUNTIF(O11:AS11,"T")*0.125)*2,0)/2

 

[cn117279]

This worked perfectly! Thank you very much Scott!