SOLVED

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

Copper Contributor

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

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

2 Replies
best response confirmed by ApexSurv1vor (Copper Contributor)
Solution

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

@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...

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

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

Accepted Solutions
best response confirmed by ApexSurv1vor (Copper Contributor)
Solution

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

@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...