Spreadsheet Formula Fixer

Example inputs

Spreadsheet app, version and formula separator: Excel 365, English functions, comma separator.

Column layout and a few anonymized sample rows: A1 Date, B1 Client, C1 Status, D1 Amount. Data rows 2:100. G2 contains Acme, H2 is a real Excel date 2026-09-01, I2 is a real Excel date 2026-09-30. Output J2. Row 2: 2026-09-30 15:00, Acme, Paid, 80. Row 3: 2026-10-01 00:00, Acme, Paid, 50. Row 4: blank date, Acme, Paid, 30. Row 5: 2026-09-15, Beta, Paid, 20. Row 6: 2026-09-10, Acme, Pending, 40. Row 7: 2026-09-01 00:00, Acme, Paid, 20.

Desired result, current formula and error: Sum Paid amounts for the client in G2 during H2 through I2 inclusive. Ignore blank dates. My <=I2 condition missed September 30 afternoon transactions. Expected total for these rows: 100.

Generated example

Paste-ready formula

Destination cell J2:

=SUMIFS(D2:D100,B2:B100,G2,C2:C100,"Paid",A2:A100,">="&$H$2,A2:A100,"<"&($I$2+1))

Setup and assumptions

• Input cells: G2 = client name (text), H2 = start date, I2 = end date — both stored as real Excel date serials (confirmed in layout).
• Data range: A2:A100 (dates, may include time-of-day or blanks), B2:B100 (client, text), C2:C100 (status, text), D2:D100 (amount, numeric).
• Formula goes in J2 and is not filled down (single summary cell), so H2/I2/G2 are absolute ($H$2, $I$2, G2 left relative only if you plan to reuse the row elsewhere — kept as G2 per single-cell use).
• Assumption: "Paid" spelling/case is consistent; SUMIFS text matching is case-insensitive, which is fine here.
• Blank dates in column A are automatically excluded because a blank does not satisfy `>=$H$2`.

Why it works

The interval test uses `>= start` and `< end + 1`. Adding 1 to I2 pushes the upper boundary to the start of the next day (2026-10-01 00:00:00), so any timestamp on 2026-09-30, including 15:00, is still counted. Using `<=I2` instead compares against 2026-09-30 00:00:00, silently excluding same-day afternoon entries — that is the diagnosed cause of the reported shortfall (diagnosis, based on the stated symptom and row 2's timestamp). Blank dates fail the `>=` test and are naturally skipped, satisfying "ignore blank dates" without extra logic.

Check it on these rows

Using the full table exactly as supplied (rows 2–7), only changing criteria conceptually per case — sums always drawn from the same A2:D100 data:

1. 1.Ordinary match (as given): G2=Acme, H2=2026-09-01, I2=2026-09-30 → matches rows 2 (80), 6 excluded (Pending), 7 (20). Row 6 is Pending so excluded; row 4 blank date excluded. Expected = 80 + 20 = 100. ✔ Matches stated expected total.
2. 2.Nonmatch client: G2=Zeta (no such client in data) → no rows match B2:B100. Expected = 0.
3. 3.Boundary/bug-catching case: Same H2/I2 as case 1, confirming row 2 (2026-09-30 15:00) is included. If the old `<=I2` formula were used, row 2 would be dropped, giving 20 instead of 100 — this case directly catches that original bug.

If the result is still wrong

• Text dates vs. serials: Select A2:A100 and check with a helper cell `=ISNUMBER(A2)` for each populated row. If any return FALSE, those cells are text-formatted dates and will fail the `>=`/`<` comparison silently.
• Amounts stored as text: Check `=ISNUMBER(D2)` down the amount column. Text-stored numbers are ignored by SUMIFS' sum range without error, causing an undercount with no visible warning.

Missing for full certainty: confirmation that no other status values (e.g., "paid" typo, trailing spaces) exist in column C.