How to use IF with SUMIF to compare a total with a limit in Excel
Formula (Excel and Google Sheets)
=IF(SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2,"Over limit","OK")Total the rows with SUMIF and compare inside IF: =IF(SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2,"Over limit","OK").
- Result on the example
- one result per row in
F2, filled down toF4 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F4. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMIF adds the open amounts on rows whose customer matches D2: Contoso owes 4,200 + 3,650 + 2,600 = 10,450. IF compares that total with the limit next to it. The data ranges are locked with $, while D2 and E2 move down with each customer.
=IF(SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2,"Over limit","OK")Result"Over limit"
$A$2:$A$87 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …A range: 7 cells in one column.
D2"Contoso"The value in D2.
$B$2:$B$87 rows × 1 col: $4,200, $7,800, $3,650, $2,900, …A range: 7 cells in one column.
SUMIF($A$2:$A$8,D2,$B$2:$B$8)10450SUMIF: Adds values where the range meets a condition.- range
$A$2:$A$87 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", … - criteria
D2"Contoso" - sum_range
$B$2:$B$87 rows × 1 col: $4,200, $7,800, $3,650, $2,900, …
- range
E2$10,000The value in E2.
SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2TRUEThe > operator tests greater than.
IF(SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2,"Over limit","OK")"Over limit"IF: Returns one value if a test is true, another if false.- logical_test
SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2TRUE - value_if_true
"Over limit" - value_if_false
"OK"not used
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2,"Over limit","OK"). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(SUMIFS($B$2:$B$8,$A$2:$A$8,D2)>E2,"Over limit","OK") | Over limit, OK, OK | SUMIFS gives the same total and takes more conditions later, such as only invoices past due. |
| =LET(owed,SUMIF($A$2:$A$8,D2,$B$2:$B$8),IF(owed>E2,"Over by "&TEXT(owed-E2,"#,##0"),"OK")) | Over by 450, OK, OKDifferent result | Says by how much, with the total named once using LET. Contoso shows "Over by 450". |
Common mistakes
- Leave
D2andE2unlocked so each row checks its own customer; lock only the data ranges. - Decide whether reaching the limit exactly is over (>=) or not (>).
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Use IF with SUMIF to compare a total with a limit
F2, total the open amounts for the customer in D2 and show "Over limit" when the total is above their credit limit in E2, otherwise "OK". Fill down to F4.Questions
How do I show the credit left?
=MAX(0,E2-SUMIF($A$2:$A$8,D2,$B$2:$B$8)).
How do I flag each invoice of a customer who is over?
Next to each invoice: =IF(SUMIF($A$2:$A$8,A2,$B$2:$B$8)>XLOOKUP(A2,$D$2:$D$4,$E$2:$E$4),"Over limit","").
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.