Medium: SUMIF spaces and case
SUMIF for "Paid" misses “paid”, “ Paid”, and “PAID”. Clean statuses. Broken total 14000 → fixed 18100 (LibreOffice verified).
SUMIF "Paid" साठी “paid”/spaces चुकवते. Clean करा. 14000 → 18100.
SUMIF "Paid" “paid”/spaces छोड़ देता है। Clean करें। 14000 → 18100।
Why → What → How
Why: Exports mix case and pad status text. SUMIF exact match then under-counts.
What: Six claims; five should be Paid after clean. LO: 14000 → 18100.
How:
- Open Claims.
- Scan Status for spaces/case.
- TRIM + proper case to Paid/Open.
- Re-run SUMIF → 18100.
Ravindra Bagale’s Tip
Helper =TRIM(UPPER(C5)) then SUMIF on a canonical code, or clean once with Find/Replace.
Ravindra Bagale’s Tip – मराठी
TRIM+UPPER helper किंवा Find/Replace ने एकदा clean करा.
Ravindra Bagale’s Tip – हिंदी
TRIM+UPPER helper या Find/Replace से एक बार clean करो।
Bugs explained (with mini examples)
Bug 1 — Exact status mismatch
Bug 2 — Trusting the SUMIF total
Practice tasks (12)
- Task 1. Broken SUMIF result?Hint: 14000.
- Task 2. Fixed target?Hint: 18100.
- Task 3. Find the leading-space status.Hint: C-03.
- Task 4. Normalize PAID/paid.Hint: Paid.
- Task 5. Re-SUMIF.Hint: 18100.
- Task 6. Interview sentence.Hint: “Status text inconsistent.”
- Task 7. Does SUMIF ignore case in Excel?Hint: Often case-insensitive — still fix spaces.
- Task 8. TRIM column.Hint: Helper.
- Task 9. Count Open after clean.Hint: 1.
- Task 10. Data validation list Paid/Open.Hint: Prevent drift.
- Task 11. Bonus: pivot by Status.Hint: See splits.
- Task 12. Document clean rule.Hint: A14.
In-browser formula check
Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.
Step-by-step solution (try first)
- Clean Status to Paid/Open.
- SUMIF → 18100. LO: 14000 → 18100.
Quick quiz