Excel, how to add/detect a coloured cell as only number "1" and ignore the cell value

[ApexSurv1vor]

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

Thanks in advance!

[#2 · Accepted solution]

@ApexSurv1vor It's not entirely clear how your data is setup, but the COUNTIF function might work. For example:

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

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

COUNTIF Example

Please see the attached sample workbook, if needed...

[#3]

Yes that is exactly what i was looking for thankyou so much!