# How to use IF with SUMIF to compare a total with a limit in Excel

Total the rows with SUMIF and compare inside IF: =IF(SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2,"Over limit","OK").

**Excel:** `=IF(SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2,"Over limit","OK")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Customer | Open amount |  | Customer | Credit limit | Status |
| Contoso | 4200 |  | Contoso | 10000 |  |
| Fabrikam | 7800 |  | Fabrikam | 15000 |  |
| Contoso | 3650 |  | Northwind | 5000 |  |
| Northwind | 2900 |  |  |  |  |
| Fabrikam | 5100 |  |  |  |  |
| Contoso | 2600 |  |  |  |  |
| Northwind | 1450 |  |  |  |  |

Formula in F2, filled to F4: `=IF(SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2,"Over limit","OK")`

Result: Over limit; OK; OK

## 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.

## Other ways

- `=IF(SUMIFS($B$2:$B$8,$A$2:$A$8,D2)>E2,"Over limit","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"))`: Says by how much, with the total named once using LET. (Contoso shows "Over by 450".)

## Common mistakes

- Leave `D2` and `E2` unlocked so each row checks its own customer; lock only the data ranges.
- Decide whether reaching the limit exactly is over (>=) or not (>).

## 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","")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/if-with-sumif-compare-total-to-limit