Statistical & Conditional Counting
Sum Text Values Like Numbers (T-Shirt Story Points & Ordinal Rating Scales)
Total and average categorical text codes such as XS/S/M/L/XL story points or Low/Medium/High risk tiers by pairing a compact lookup legend with COUNTIF frequencies and SUMPRODUCT.
Translating categorical labels into weighted numeric totals without per-ticket clutter
Agile sprint boards, QA severity logs, and vendor scorecards frequently store human-readable text codes ("XS", "S", "M", "L", "XL" or "Poor".."Excellent") that SUM(B2:B7) ignores completely because SUM skips text strings.
Maintaining a compact 5-row scale table (Code in D2:D6, Numeric Weight in E2:E6, and Occurrence Count =COUNTIF($B$2:$B$7, D2) in F2:F6) lets SUMPRODUCT(E2:E6, F2:F6) compute total weighted points, average score per ticket, and unmapped-code validation errors in one transparent block.
| Size Code | Fibonacci Weight | Ticket Count | Weighted Points |
|---|---|---|---|
| XS | 1 | 1 | 1 |
| S | 2 | 1 | 2 |
| M | 3 | 2 | 6 |
| L | 5 | 1 | 5 |
| XL | 8 | 1 | 8 (Total = 22 pts) |
Scale Frequency & Weighted Total Formula
Count each tier’s occurrences with COUNTIF($B$2:$B$7, D2) and take the inner product SUMPRODUCT(E2:E6, F2:F6) against your point weights.
=SUMPRODUCT(E2:E6, F2:F6)Unmapped / Typo Label Audit Formula
Subtract recognized tier counts SUM(F2:F6) from COUNTA(B2:B7) so an unexpected label like "XXL" or "Med" immediately flags a non-zero discrepancy.
=COUNTA(B2:B7) - SUM(F2:F6)Try the example
Use the original values or change them to test your understanding.