01 / 11The Provision Was Bigger Than the Debt

SUMIFSExcelCredit Control

SUMIFS With OR Criteria in Excel Without Double Counting

The Bad-Debt Provision Came to £6,980 More Than Every Customer Owed, Because Two SUMIFS Added Together Counted Three Invoices Twice

30 Oct 20268 min read

UsesSUMIFSSUMPRODUCTAND, OR, NOTFILTER

Brindle Foods provides in full for any invoice more than 90 days overdue or in dispute. At the September year end the sheet set aside more than its customers owed. Every invoice and every condition was right; the formula added two totals that overlapped.

Year-end provision, 30 SeptemberFigure
Open invoices14
Debtors ledger£83,715.50
Over 90 days£47,400.50
In dispute£43,295.00
Provision booked£90,695.50
Provision due£60,800.50
Overstated by£29,895.00

Scope. SUMIFS, SUMPRODUCT and OR work in every current version of Excel. FILTER in section 6 needs Excel 2021 or Microsoft 365.

Throughout, the ledger is in A1:G15: invoice in A, customer in B, amount in C, days overdue in D and Yes or No for a dispute in E.

01The Provision Was Bigger Than the Debt

The policy is one sentence: provide 100% for an invoice that is over 90 days overdue, or that the customer disputes. The finance assistant built it as two SUMIFS, one per condition, and added them.

=SUMIFS(C2:C15,D2:D15,">90")+SUMIFS(C2:C15,E2:E15,"Yes")

It returned £90,695.50. The whole ledger is £83,715.50, so the sheet expected to lose £6,980.00 more than anyone owed. The journal was posted, and the draft accounts went to the board £29,895.00 short on profit.

Fourteen Open Invoices, £83,715.50 Owed and £90,695.50 Provided

Brindle Foods' debtors ledger at 30 September, in A1:G15. Column C is the amount owed, D the days overdue and E whether the customer disputes it. Column F is what the two added SUMIFS provided for each invoice, and G what the policy says: the full amount once if the invoice is over 90 days or disputed.

ABCDEFG
1
Invoice
Customer
Amount
Days overdue
Disputed
Provision booked
Provision due
2
INV-2201
Ashby Deli
4180
34
No
0
0
3
INV-2203
Corran Hotels
12640
118
Yes
25280
12640
4
INV-2207
Fenwick Catering
2935.5
97
No
2935.5
2935.5
5
INV-2209
Holt & Sons
8410
12
Yes
8410
8410
6
INV-2212
Marlow Café
1760
63
No
0
0
7
INV-2215
Pryor Schools
9875
104
Yes
19750
9875
8
INV-2218
Quayside Bistro
3320
91
No
3320
3320
9
INV-2220
Tenby Care Homes
6045
45
No
0
0
10
INV-2223
Ashby Deli
2210
90
No
0
0
11
INV-2226
Corran Hotels
7380
132
Yes
14760
7380
12
INV-2229
Holt & Sons
5115
26
No
0
0
13
INV-2231
Marlow Café
4990
76
Yes
4990
4990
14
INV-2234
Fenwick Catering
3605
8
No
0
0
15
INV-2237
Tenby Care Homes
11250
99
No
11250
11250

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

The auditor found it at the analytical review, by putting the provision beside the ledger. Nobody at Brindle had, because the two figures lived on different tabs.

02Two SUMIFS Add, They Do Not Combine

Each SUMIFS is right on its own. The first totals the six invoices over 90 days, the second the five in dispute. Adding them counts every invoice in both groups twice, because nothing tells the second total what the first already took.

GroupInvoicesAmount
Over 90 days6£47,400.50
In dispute5£43,295.00
Both3£29,895.00
Either, counted once8£60,800.50

Six plus five is eleven, but only eight invoices meet the policy. INV-2203, INV-2215 and INV-2226 are old and disputed, and each one went into the provision twice.

Inside one SUMIFS, every criterion is AND. There is no OR switch, which is why people reach for the plus sign.

Scenario: Enter =COUNTIFS(D2:D15,">90")+COUNTIFS(E2:E15,"Yes") beside the ledger and note that it returns 11. Then count by eye the invoices that meet either condition, and find the three that make up the difference.

Try it in the grid

03Subtract the Overlap

The quickest repair keeps both totals and takes away the part they share. A third SUMIFS with both conditions measures exactly what was counted twice.

=SUMIFS(C2:C15,D2:D15,">90",E2:E15,"Yes")

That returns £29,895.00, the three invoices in both groups. Subtract it from the sum of the other two:

=SUMIFS(C2:C15,D2:D15,">90")+SUMIFS(C2:C15,E2:E15,"Yes")
 -SUMIFS(C2:C15,D2:D15,">90",E2:E15,"Yes")

The result is £60,800.50. This works for two conditions. With three it needs seven SUMIFS, adding and subtracting in turn, and nobody gets that right at year end.

Scenario: Enter the overlap SUMIFS on its own first and check it returns 29,895.00. Then build the three-part formula and confirm the provision falls to 60,800.50.

Try it in the grid

04Count the Row, Not the Conditions

SUMPRODUCT can test each row against both conditions and decide once whether it qualifies. Each comparison returns TRUE or FALSE, and adding them gives 0, 1 or 2 per row.

=SUMPRODUCT(C2:C15*(((D2:D15>90)+(E2:E15="Yes"))>0))

The >0 is the whole fix. It turns 2 back into a single yes, so INV-2203 counts once like every other qualifying row. Leave it out and the formula repeats the original mistake.

Adding conditions this way scales: a third condition is one more bracket inside the sum, not four more SUMIFS. To count invoices instead of summing them, drop the amounts.

=SUMPRODUCT(--(((D2:D15>90)+(E2:E15="Yes"))>0))

That returns 8, where the two added COUNTIFS returned 11.

Scenario: Enter the SUMPRODUCT with >0 and check it returns 60,800.50. Then delete >0 and its brackets, and watch it climb back to 90,695.50.

Try it in the grid

05A Helper Column You Can Read

A single formula is hard to audit. A column that says, row by row, whether each invoice qualifies is easy to audit. Put this in H2 and fill it down:

=OR(D2>90,E2="Yes")

OR returns one TRUE however many of its tests pass. The provision is then an ordinary SUMIFS on that column, =SUMIFS(C2:C15,H2:H15,TRUE), and =COUNTIF(H2:H15,TRUE) returns 8.

If you would rather see amounts than flags, put the amount in the column directly. That is how column G of the example was built:

=IF(OR(D2>90,E2="Yes"),C2,0)

A reviewer can filter on TRUE and tick off eight invoices against the policy. Nobody can do that with a number that came out of one cell.

Scenario: Add the OR column in H and filter it to TRUE. Check that eight invoices remain, that INV-2223 at exactly 90 days is not one of them, and that =SUMIFS(C2:C15,H2:H15,TRUE) returns 60,800.50.

Try it in the grid

06FILTER Returns Each Row Once

In Excel 2021 or Microsoft 365, FILTER takes the same added conditions and returns every row where the sum is not zero. A row that meets both conditions is still one row.

=SUM(FILTER(C2:C15,(D2:D15>90)+(E2:E15="Yes")))

FILTER treats any non-zero include value as a yes, so the 2 for INV-2203 needs no >0. The total is £60,800.50.

Drop the SUM and the same formula lists the eight amounts, which is a useful thing to print beside the journal. FILTER(A2:C15, …) lists the invoices too.

07OR Within One Column Is Safe

The double count needs two columns. When both conditions test the same column, no row can meet both, so adding is fine. A credit-hold list in H2:H3 naming Corran Hotels and Tenby Care Homes totals cleanly:

=SUM(SUMIFS(C2:C15,B2:B15,H2:H3))

SUMIFS runs once per name and SUM adds the results: £20,020.00 for Corran and £17,295.00 for Tenby, £37,315.00 in all. An invoice belongs to one customer, so nothing overlaps.

It breaks the moment the list repeats itself. Type Corran Hotels in both H2 and H3 and the total doubles to £40,040.00, with no error.

Overlapping bands are the same problem in one column. ">30" and ">90" on the days column overlap for every invoice over 90 days. The test is not the column; it is whether one row can pass both criteria.

Scenario: Put Corran Hotels in H2 and Tenby Care Homes in H3, then enter =SUM(SUMIFS(C2:C15,B2:B15,H2:H3)) and check it returns 37,315.00. Change H3 to Corran Hotels and watch the total double.

Try it in the grid

08Check the Provision Against the Debt

Two checks would have stopped the journal. No invoice can be provided for more than it is worth, and no provision can exceed the ledger it provides for.

=SUM(F2:F15)<=SUM(C2:C15)

On Brindle's sheet this returns FALSE. Per invoice, =SUMPRODUCT(--(F2:F15>C2:C15)) returns 3, one for each invoice that was provided twice.

The first check catches the extreme case only: an overlap smaller than the unprovided invoices would pass it. The second catches every double count, so put it beside the journal, not on a tab nobody opens.

Scenario: Enter =SUMPRODUCT(--(F2:F15>C2:C15)) above column F and check it returns 3. Then replace column F with the IF(OR(…)) formula from section 5 and confirm it falls to 0.

Try it in the grid

09Six Traps

  1. Adding SUMIFS for OR. Rows that meet both conditions count twice, with no error.
  2. Dropping the >0. SUMPRODUCT with added conditions and no >0 repeats the double count.
  3. Multiplying instead of adding. * between conditions is AND, not OR; it returns only the £29,895.00 overlap.
  4. A repeated name in a criteria list. SUM(SUMIFS(…,H2:H3)) counts a customer twice if the list names them twice.
  5. Overlapping bands in one column. ">30" plus ">90" double-counts the oldest invoices.
  6. >90 against "90 days or more". INV-2223 is exactly 90 days, so the policy's wording decides whether it is in.

10Four Exercises

  1. Reproduce it. Add two SUMIFS for over 90 days and disputed, and confirm 90,695.50.
  2. Measure the overlap. Write the SUMIFS with both conditions and subtract it.
  3. Count each row once. Use SUMPRODUCT with >0, then the OR helper column, and check both return 60,800.50.
  4. Prove it. Add the per-invoice check and confirm it flags INV-2203, INV-2215 and INV-2226 and nothing else.

What to Take Away

Brindle's ledger was right and both of its totals were right. The sheet added them, and an invoice that was old and disputed went into the provision twice.

OR across two columns is not a plus sign. Decide once per row whether it qualifies, with OR, >0 or FILTER, and then total the rows. And put the provision beside the ledger, because it can never be the bigger number.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge