Count category labels and calculate their share of nonblank responses

Turn a raw log of category tags into frequency counts and % shares!

Each nonblank text label is one response. Empty cells and formulas returning an empty string are excluded; numbers and other nontext entries are invalid in this text-category example. Column B validates each source row: 1 for text, 0 for blank, -1 for invalid data.

COUNTIF matching is case-insensitive. Escape literal wildcard characters and prefix the criterion with = so a label such as >5 is treated as a label rather than a comparison. Spaces are preserved; normalize them upstream if that is your intended policy.

Percentages add to 100% only when the summary contains every distinct category exactly once under these matching rules. Do not round the underlying ratios; use percentage formatting for display. Displayed rounded percentages can still sum to 99.99% or 100.01%.

Prepare the validation helpers

Enter in B2 and fill down through B9. These helpers are already populated in the practice workbook.

=IF(COUNTBLANK(A2)>0,0,IF(ISTEXT(A2),1,-1))

Category Share of Total Responses (Decimal %)

Divide by the sum of validated response flags in column B. Return zero for an entirely blank source. Format the result as a percentage; keep its full precision.

=IF(OR(NOT(ISTEXT(C2)),COUNTBLANK(C2)>0),"Enter category",IF(COUNTIF($B$2:$B$9,-1)>0,"Invalid inputs",IF(SUM($B$2:$B$9)=0,0,COUNTIF($A$2:$A$9,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"~","~~"),"*","~*"),"?","~?"))/SUM($B$2:$B$9))))

Category Frequency Count

Match the literal label in C2, ignoring case but preserving spaces. The fixed source range can be reused for each summary row.

=IF(OR(NOT(ISTEXT(C2)),COUNTBLANK(C2)>0),"Enter category",IF(COUNTIF($B$2:$B$9,-1)>0,"Invalid inputs",COUNTIF($A$2:$A$9,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"~","~~"),"*","~*"),"?","~?"))))

Further reading

• Microsoft Support — COUNTIF function