A regular SUM includes paid invoices and can overstate a receivables balance. Use SUMIF to test each invoice status and add only the paired amounts whose status is Unpaid.
Quick answer: To total unpaid invoice amounts, enter =SUMIF(A1:A3,"Unpaid",B1:B3) in D1. With A1 and A3 set to Unpaid and amounts of 1250 and 450, D1 should display 1700.
Microsoft describes SUMIF as a way to sum values that meet one condition and documents the function’s syntax, criteria, and sum-range behavior.
Formula and setup
Use this formula when column A contains statuses and column B contains the corresponding invoice amounts:
=SUMIF(A1:A3,"Unpaid",B1:B3)
- Select the output cell, D1.
- Enter
=SUMIF(A1:A3,"Unpaid",B1:B3). - Press Enter. Format D1 as currency if the Amount values represent currency.
The first range, A1:A3, contains the values to evaluate. The criterion, "Unpaid", specifies the status to include. The final range, B1:B3, contains the amounts to add. Keep the ranges aligned: A1 pairs with B1, A2 with B2, and A3 with B3. For a broader explanation of this pattern, see this guide to conditional sums in Excel.
Worked unpaid-invoice example
Synthetic input: A1 is Unpaid with B1 1250; A2 is Paid with B2 800; A3 is Unpaid with B3 450.
| Row | Status cell | Status | Amount cell | Amount | Included? |
|---|---|---|---|---|---|
| 1 | A1 | Unpaid | B1 | 1250 | Yes |
| 2 | A2 | Paid | B2 | 800 | No |
| 3 | A3 | Unpaid | B3 | 450 | Yes |
In D1, use =SUMIF(A1:A3,"Unpaid",B1:B3).
SUMIF checks each status in A1:A3 against the criterion “Unpaid” and adds the corresponding amounts from B1:B3. The matching rows are row 1 and row 3, so the expected unpaid total is 1700.
Although B2 is a number, it is not included because its paired status, A2, is Paid. The row-by-row relationship between the criteria range and sum range is the key control in this formula.
Boundary checks and common mistakes
No unpaid invoices produces 0
If no status is Unpaid, =SUMIF(A1:A3,"Unpaid",B1:B3) should display 0; that is a valid result, not an error.
| Cell | Value |
|---|---|
| A1 | Paid |
| B1 | 320 |
| A2 | Sent |
| B2 | 275 |
| A3 | Pending |
| B3 | 900 |
With those values, the same formula in D1 has an expected result of 0. Confirm the intended status labels before treating a zero as a reporting problem.
Different status labels are excluded
A different status label, such as Awaiting Payment, does not meet the criterion “Unpaid” and its amount is excluded. Standardize the status labels or use an additional explicit condition when the report must include more than one label.
Misaligned ranges can return the wrong rows
Use criteria and amount ranges that cover the same rows. For example, use A1:A20 with B1:B20, not A1:A20 with B1:B3. Microsoft explains that a sum_range that differs in size from the criteria range is summed from its first cell using the criteria range’s dimensions; keeping both ranges the same shape avoids that ambiguity.
Compatibility and limits
Microsoft lists SUMIF for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, and Excel 2016.
This formula handles one condition only: it cannot restrict the total by both Status and another field such as due date or customer. For example, to total only unpaid invoices for one customer, use SUMIFS instead. Microsoft distinguishes SUMIF for one condition and SUMIFS for multiple conditions. You can also compare the two functions in this SUMIF and COUNTIF guide.
This method does not resolve inconsistent reporting labels such as Unpaid, Awaiting Payment, and Open. Decide which labels belong in the outstanding-balance report, standardize them where possible, or define the required conditions explicitly before relying on the total.
Practice with the exact example data
Use a separate blank worksheet for each example. Enter each value at its listed cell address, then type the formula in AA1. Quoted text is shown in JSON notation to preserve spaces: enter the characters inside the quotes, without the quotes, using a leading apostrophe to keep text and spaces literal. An empty quoted string means an explicit empty text value; use an apostrophe entry where appropriate. Compare your result with the expected result below. These are synthetic practice data.
Example 1 — Main case
| Cell | Input value |
|---|---|
| A1 | “Unpaid” |
| B1 | 1250 |
| A2 | “Paid” |
| B2 | 800 |
| A3 | “Unpaid” |
| B3 | 450 |
Formula in AA1: =SUMIF(A1:A3,"Unpaid",B1:B3)
Expected result: 1700
Example 2 — Boundary case
| Cell | Input value |
|---|---|
| A1 | “Paid” |
| B1 | 320 |
| A2 | “Sent” |
| B2 | 275 |
| A3 | “Pending” |
| B3 | 900 |
Formula in AA1: =SUMIF(A1:A3,"Unpaid",B1:B3)
Expected result: 0
These examples were checked with a limited calculator supporting numeric aggregates, ASCII text functions, exact criteria and exact first-match XLOOKUP. Only the displayed examples and supported semantics were calculated. They were not executed in Microsoft Excel. This check does not verify every claim in the article.
Prepared with automated drafting and review, official references, and a limited example calculator. Synthetic examples are provided for practice; no independent human review or Microsoft Excel execution is claimed.