How to sum values if the date is before a certain date in Excel
Formula (Excel and Google Sheets)
=SUMIF(B2:B8,"<"&F1,C2:C8)Use SUMIF with "<" joined to the date cell: =SUMIF(B2:B8,"<"&F1,C2:C8).
- Result on the example
- $5,190 in
F2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMIF(B2:B8,"<"&F1,C2:C8)Result5190
B2:B87 rows × 1 col: 5/15/2026, 6/2/2026, 6/30/2026, 7/1/2026, …A range: 7 cells in one column.
F17/1/2026The value in F1.
"<"&F1"<46204"The & operator joins as text.
C2:C87 rows × 1 col: $1,840, $620, $2,275, $940, …A range: 7 cells in one column.
SUMIF(B2:B8,"<"&F1,C2:C8)5190SUMIF: Adds values where the range meets a condition.- range
B2:B87 rows × 1 col: 5/15/2026, 6/2/2026, 6/30/2026, 7/1/2026, … - criteria
"<"&F1"<46204" - sum_range
C2:C87 rows × 1 col: $1,840, $620, $2,275, $940, …
- range
In Google Sheets
The same formula works in Google Sheets: =SUMIF(B2:B8,"<"&F1,C2:C8). We ran it in Google Sheets mode on the same data and it returns $5,190.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS(C2:C8,B2:B8,"<"&F1) | $5,190 | SUMIFS puts the sum range first and lets you add a status condition later. |
| =SUMIF(B2:B8,"<="&F1,C2:C8) | $6,130Different result | 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.
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.
Sum values if the date is before a certain date
F2, total the invoices due before the date in F1.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).
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.