Excel, how to add a coloured cell as only "1" and ignore the cell value, w/ multiple colours?

Copper Contributor

Hey, ive got a attendance report with up to 10 weeks per person. Right now i have 2 sepertae formulas, 1 which uses cell value to determine colour (so 1=red (absent), 2=green (present) etc.) And another formula that uses the "total weeks present" ('4/10) to determine a percentage (=40%). Is there any way i can get the "total weeks present" to increase by 1 (from'4/10 to '5/10) when i fill in the cell with present (2) and not increase the "total weeks present" by 2 (almost ignoring the cell value, but detecting the cell is full or something like that)?

Also is there any way to make it so 2 different numbers add up as well, so that 1=absent(doesnt add up) 2=present(adds up) and 3=late(adds up)?

=COUNTIF(range, 2) & "/10"

=COUNTIF(B2:K2, 2) & "/" & COLUMNS($B$1:$K$1)


2 Replies


See the attached workbook.


Hey, my bad for not mentioning, i am also using google spreadsheets, and there is no fraction formatting (that i am aware of anyway) so the result stays as (0.40 instead of 4/10).