Your formula
Goal: In column F of Orders, show each salesperson's total for orders with Status "Paid" and dated January 1, 2024 or later. Then on Summary, list each salesperson once, starting at A2, with that same total beside them.
Step 1: Running total in column F (Orders tab)
Type a header in Orders!F1, such as Paid Total 2024+. Then click Orders!F2 and paste:
=SUMIFS($D$2:$D$1000,$C$2:$C$1000,C2,$E$2:$E$1000,"Paid",$B$2:$B$1000,">="&DATE(2024,1,1))
Fill it down: Double-click the small square at the bottom-right corner of F2, or drag it down to your last row.
Step 2: List of salespeople (Summary tab)
Type Salesperson in Summary!A1 and Total in Summary!B1. Then click Summary!A2 and paste:
=SORT(UNIQUE(FILTER(Orders!C2:C1000,Orders!C2:C1000<>"")))
This spills automatically, meaning the results flow down into the cells below by themselves. Don't fill it down.
Step 3: Totals beside each name (Summary tab)
Click Summary!B2 and paste:
=SUMIFS(Orders!D2:D1000,Orders!C2:C1000,A2#,Orders!E2:E1000,"Paid",Orders!B2:B1000,">="&DATE(2024,1,1))
This also spills automatically. It gives one total for each name in column A.
How it works
SUMIFS (Steps 1 and 3)
| Part | What it does |
|---|---|
SUMIFS( |
Adds up numbers only from rows that meet all the conditions you list. |
$D$2:$D$1000 |
The numbers to add: the Amount column. |
$C$2:$C$1000, C2 |
Condition 1: Salesperson must match the name in this row (C2). |
$E$2:$E$1000, "Paid" |
Condition 2: Status must be "Paid" (capital letters don't matter). |
$B$2:$B$1000, ">="&DATE(2024,1,1) |
Condition 3: Date must be on or after Jan 1, 2024. DATE(2024,1,1) builds that date, and ">="& joins it with "greater than or equal to." |
A2# (Step 3 only) |
The # means "the whole spilled list starting at A2," so you get one total per name. |
Spill list (Step 2)
| Part | What it does |
|---|---|
FILTER(...,Orders!C2:C1000<>"") |
Keeps only non-empty salesperson cells, so blank rows below your data are ignored. |
UNIQUE(...) |
Removes duplicates, so each name appears once. |
SORT(...) |
Puts the names in A-to-Z order. |
About the $ signs: In Step 1, $D$2:$D$1000 is an absolute reference. The $ locks the range so it stays the same when you fill the formula down. C2 has no $ on purpose, so it changes to C3, C4, and so on, and each row looks up its own salesperson.
Check against your data
Step 1 (column F on Orders), checked by hand:
| Row | Inputs | Expected result |
|---|---|---|
| 2 | Maria, 12/15/2023, 250.00, Paid | 300.00: her 2023 order is excluded, order 1003 is Pending, so only order 1004 (300.00) counts |
| 3 | Devon, 01/04/2024, 480.50, Paid | 480.50: order 1006 (210.00) is Refunded, so it's excluded |
| 6 | Lee, 02/11/2024, 90.00, Paid | 705.25: 90.00 + 615.25 |
Every row shows the salesperson's overall total, so all of Maria's rows show 300.00. That's expected.
Steps 2 and 3 (Summary tab), based on your 7 sample rows:
| Cell | Salesperson | Total |
|---|---|---|
| Row 2 | Devon | 480.50 |
| Row 3 | Lee | 705.25 |
| Row 4 | Maria | 300.00 |
With your full data, the totals will include rows I can't see.
If you have an older version or Google Sheets
You're on Excel 365, so the main formulas will work for you. If you ever share the file:
- Step 1 works as-is in Excel 2016 and Google Sheets.
- Excel 2016: It has no UNIQUE or SORT. Copy column C to Summary!A2 and use Data > Remove Duplicates. Then put this in B2 and fill down:
=SUMIFS(Orders!$D$2:$D$1000,Orders!$C$2:$C$1000,A2,Orders!$E$2:$E$1000,"Paid",Orders!$B$2:$B$1000,">="&DATE(2024,1,1)) - Google Sheets: The Step 2 formula works. Sheets doesn't support
A2#, so use the Excel 2016 B2 formula above and fill it down.
If you see an error
| Error | Likely cause | Fix |
|---|---|---|
| #SPILL! | Something is already typed in the cells below A2 or B2 on Summary. | Clear those cells so the list has room to spill. |
| #NAME? | A typo in a function name, or the tab name is misspelled. | Check that the tab is named exactly Orders and that the formula was pasted in full. |
| #CALC! | FILTER found no salesperson names at all. | Check that names are in Orders column C, rows 2 onward. |
| #REF! in B2 | A2 doesn't contain the spill formula, so A2# has nothing to point to. |
Make sure the Step 2 formula is in A2 itself. |
| Total is 0 or too low | Dates are stored as text, not real dates. | Type =ISNUMBER(Orders!B2) in a spare cell. If it shows FALSE, select column B and use Data > Text to Columns > Finish to convert. |
| Total is 0 or too low | Amounts are stored as text (often shown with a green triangle). | Select column D, click the warning icon, and choose Convert to Number. |
| A Paid order isn't counted | There's an extra space, for example "Paid ". |
Clean the Status column with Find & Replace, or use a helper column with =TRIM(E2). |
| The same person appears twice on Summary | The name is spelled differently or has a stray space, such as "Lee" and "Lee ". | Fix the spelling or spaces in column C. |
Assumptions I made
- Headers are in row 1 of Orders, with data in rows 2–1000: A = Order ID, B = Date, C = Salesperson, D = Amount, E = Status. Extend the
1000if you have more rows. - Dates are real Excel dates in month/day/year order, as in your sample.
- "2024 or later" includes all dates from Jan 1, 2024 onward, including future years. If you want 2024 only, add this condition inside each SUMIFS before the final
):,Orders!B2:B1000,"<"&DATE(2025,1,1). In Step 1, use$B$2:$B$1000instead. - The Summary lists every salesperson, even someone whose total is 0 (for example, a person with only Pending orders). If you'd rather list only people with qualifying sales, let me kno