Build conditional reporting rules — Smart Study Module 2

Your manager asks for January collections without accidentally including February.

Outcomes

• Use inclusive/exclusive date boundaries
• Separate missing input from zero
• Reconcile SUMIFS and COUNTIFS

Understand

Use SUMIFS for several conditions on aligned ranges. A half-open date interval includes every January day, including timestamps late on 31 January, without admitting February.

IF describes a deliberate business decision. Avoid IFERROR around the entire model: first establish whether errors indicate a missing ID, a zero denominator or invalid data. Preserve a useful exception list.

TRIM removes ordinary extra spaces, not every Unicode character. Check the cleaned identifier against the original. For DPD buckets use explicit boundaries: 0, 1–30, 31–60, 61–89 and 90+.

Follow along

1. Name the payments table Payments.
2. Put DATE(2026,1,1) in H2 and DATE(2026,2,1) in H3.
3. Calculate January using both date criteria.
4. Change the end bound to March and explain the changed result.

Worked example

=SUMIFS(Payments[amount],Payments[payment_date],">="&H2,Payments[payment_date],"<"&H3)

January ₹48,000; all supplied periods ₹98,000. Count January events: 5.

Practice

Return the January payment total.

Create a free personalized study plan · Download project instructions

Interactive practice runs in your browser.