Skip to content

Medium challenge

Medium: COUNTIFS missing OR city

By Ravindra Bagale · Free · 2 November 2026

Need Road in Nashik OR Pune. Broken formula only counts Nashik → 2. Fixed sums two COUNTIFS → 3 (LibreOffice verified).

About this data: Made-up shipments for Rohan Deshmukh (Nashik) and Aisha Khan (Dubai). Practice only.

Download broken workbook Download fixed (after you try)

Why → What → How

Why: COUNTIFS is AND across criteria. OR across cities needs adding separate COUNTIFS (or SUMPRODUCT).

What: Eight shipments. Broken 2, fixed 3 (adds Pune Road S3).

How:

Open Shipments.
List Road rows in Nashik and Pune.
Add COUNTIFS for Pune.
Confirm 3.

Ravindra Bagale’s Tip

Remember: one COUNTIFS = AND. OR = add counts (or use + inside SUMPRODUCT).

Bugs explained (with mini examples)

Bug 1 — Single-city COUNTIFS

Missed Pune Road.

Bug 2 — Assuming COUNTIFS does OR

It does not.

Practice tasks (12)

Task 1. Broken count?
Hint: 2.
Task 2. Fixed count?
Hint: 3.
Task 3. Which row was missed?
Hint: S3 Pune Road.
Task 4. Write OR formula.
Hint: COUNTIFS+COUNTIFS
Task 5. Interview line.
Hint: “OR needed two COUNTIFS.”
Task 6. Count Air Dubai.
Hint: 1.
Task 7. Would FILTER work in Excel 365?
Hint: Yes — still teach COUNTIFS.
Task 8. SUMPRODUCT alternative.
Hint: Optional.
Task 9. Add Mumbai Road to OR.
Hint: Third COUNTIFS.
Task 10. Why not COUNTIF only on Mode?
Hint: Ignores city.
Task 11. Bonus: pivot City×Mode.
Hint: Cross-check.
Task 12. Note the OR rule.
Hint: A16.

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.

Nashik OR Pune Road count

Step-by-step solution (try first)

Add Pune COUNTIFS.
Total 3. LO: 2 → 3.