Display a Checkmark When Checklist Conditions Pass

Define completion for this checklist

This four-task example counts a row as complete when reviewer text is present and its numeric QA score is at least 95 out of 100. A name typed in a cell is not an authenticated approval or electronic signature; the badge reports only these spreadsheet conditions.

The literal "✓" is portable between Excel and Arte Sheets. Excel CHAR accepts codes 1–255, so CHAR(10003) is not the correct Excel formula. Excel UNICHAR(10003) is the Unicode alternative; the runnable example uses the literal symbol.

Validate reviewer and score before showing the badge

D2 rejects blank, formula-empty, whitespace-only and numeric reviewer entries. It normalizes nonbreaking spaces for this check. A score must be a numeric value from 0 to 100; text, missing scores and out-of-range numbers never produce a checkmark. Copy the formula through D5.

The threshold is inclusive: 95 passes, 94.99 does not. This is an output rule, not an input-validation restriction, so users can enter invalid data and see the explanation.

=IF(COUNTBLANK(B2)>0,"Pending Sign-Off",IF(NOT(ISTEXT(B2)),"Pending Sign-Off",IF(LEN(TRIM(SUBSTITUTE(B2,CHAR(160)," ")))=0,"Pending Sign-Off",IF(NOT(ISNUMBER(C2)),"Enter numeric QA score",IF(OR(C2<0,C2>100),"Score must be 0 to 100",IF(C2>=95,"✓","Needs QA >= 95"))))))

Count completed statuses, not a second set of rules

F2 counts the actual checkmarks in D2:D5 and divides by the four planned tasks. The initial result is 0.5, displayed as 50% when percentage formatting is applied. Keep the numeric fraction unrounded for later calculations.

The denominator intentionally includes every planned row, including an incomplete or unnamed task. If rows are added or removed, update both the status and task ranges together. This fixed example does not automatically discover a growing table.

=COUNTIF(D2:D5,"✓")/ROWS(A2:A5)

Further reading

• Microsoft — CHAR function
• Microsoft — UNICHAR function