How to sum values for the most recent date in Excel
Formula (Excel and Google Sheets)
=SUMIFS(C2:C9,A2:A9,MAX(A2:A9))Use MAX to find the latest date and SUMIFS to add its rows: =SUMIFS(C2:C9,A2:A9,MAX(A2:A9)).
- Result on the example
- 338 in
F1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.
How it works
Dates are numbers, so MAX(A2:A9) is the latest date: June 10. SUMIFS then adds the units on every row with exactly that date, across all stores. When the next day's rows are added, the total moves to the new date by itself.
=SUMIFS(C2:C9,A2:A9,MAX(A2:A9))Result338
C2:C98 rows × 1 col: 142, 96, 158, 104, …A range: 8 cells in one column.
A2:A98 rows × 1 col: 6/8/2026, 6/8/2026, 6/9/2026, 6/9/2026, …A range: 8 cells in one column.
MAX(A2:A9)46183MAX: Largest number.- number1
A2:A98 rows × 1 col: 6/8/2026, 6/8/2026, 6/9/2026, 6/9/2026, …
- number1
SUMIFS(C2:C9,A2:A9,MAX(A2:A9))338SUMIFS: Adds values that meet every condition.- sum_range
C2:C98 rows × 1 col: 142, 96, 158, 104, … - criteria_range1
A2:A98 rows × 1 col: 6/8/2026, 6/8/2026, 6/9/2026, 6/9/2026, … - criteria1
MAX(A2:A9)46183
- sum_range
In Google Sheets
The same formula works in Google Sheets: =SUMIFS(C2:C9,A2:A9,MAX(A2:A9)). We ran it in Google Sheets mode on the same data and it returns 338.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((A2:A9=MAX(A2:A9))*C2:C9) | 338 | The same test as an array; easy to combine with a store condition. |
Common mistakes
- If the dates include times, each row has a different value and MAX matches only one row. Compare whole days with
INT(A2:A9)=INT(MAX(A2:A9))in SUMPRODUCT. - Text that looks like a date is ignored by MAX. Make sure the column holds real dates.
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 for the most recent date
F1, total the units sold on the most recent date in column A.Questions
How do I sum the latest date for one store?
Use MAXIFS for that store's latest date: =SUMIFS(C2:C9,B2:B9,"Mall",A2:A9,MAXIFS(A2:A9,B2:B9,"Mall")).
How do I sum the previous day?
Find the largest date before the latest: =SUMIFS(C2:C9,A2:A9,MAXIFS(A2:A9,A2:A9,"<"&MAX(A2:A9))).
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.