# How to sum values if the date is before a certain date in Excel

Use SUMIF with "<" joined to the date cell: `=SUMIF(B2:B8,"<"&F1,C2:C8)`.

**Excel:** `=SUMIF(B2:B8,"<"&F1,C2:C8)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Invoice | Due date | Amount |  | As of | 2026-07-01 |
| INV-501 | 2026-05-15 | 1840 |  | Overdue total |  |
| INV-502 | 2026-06-02 | 620 |  |  |  |
| INV-503 | 2026-06-30 | 2275 |  |  |  |
| INV-504 | 2026-07-01 | 940 |  |  |  |
| INV-505 | 2026-07-12 | 1310 |  |  |  |
| INV-506 | 2026-06-18 | 455 |  |  |  |
| INV-507 | 2026-08-01 | 3020 |  |  |  |

Formula in F2: `=SUMIF(B2:B8,"<"&F1,C2:C8)`

Result: $5,190

## How it works

The operator is text, so it goes in quotes and `&` joins it to the date in F1. SUMIF tests the due dates and adds the amounts on rows due before July 1. The invoice due on July 1 itself isn't overdue yet and is left out.

## Other ways

- `=SUMIFS(C2:C8,B2:B8,"<"&F1)`: SUMIFS puts the sum range first and lets you add a status condition later.
- `=SUMIF(B2:B8,"<="&F1,C2:C8)`: Use <= for "on or before". (Includes the invoice due on July 1: 6,130.)

## Common mistakes

- `"<F1"` inside the quotes compares with the text "F1" and returns 0. Keep the cell reference outside the quotes.
- Typing the date into the condition (`"<7/1/2026"`) depends on regional settings. Keep it in a cell.

## Questions

**How do I sum values after a date?**

Swap the operator: `=SUMIF(B2:B8,">"&F1,C2:C8)`.

**How do I sum invoices due before today?**

Use TODAY(): `=SUMIF(B2:B8,"<"&TODAY(),C2:C8)`.

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/sum-if-date-is-before-a-date