How to total amounts by category without repeating SUMIF formulas
Turn a detailed list into totals by customer, project or category. Calculate each group's amount and record count together, then check them against the source.
To total amounts by customer, project or area, open Refine results, add Summarize and group by that field. Add both Sum and Count in the same step. You can then check each total alongside the number of records behind it, without writing a formula for every category.
Repeated category names are not necessarily duplicates
A customer can place several orders, and a project can have several expenses. Removing all but one row per category would discard legitimate amounts. First confirm that each record represents a separate event.
This fictional example totals four records by Area, using the same amount unit throughout.
| Area | Amount |
|---|---|
| East | 120,000 |
| West | 80,000 |
| East | 50,000 |
| East | 30,000 |
Calculate a total and count together
- Load the sheet, select it as the result source and open Refine results.
- Add Summarize and select Area under Group by. Do not include Amount in the grouping fields.
- Set the first Summary method to Sum, choose Amount and name the output Total amount.
- Use Add summary within the same step, select Count and name it Records. Choose Apply.
East should total 200,000 from three records
| Area | Total amount | Records |
|---|---|---|
| East | 200,000 | 3 |
| West | 80,000 | 1 |
East is 120,000 + 50,000 + 30,000. The two totals sum to 280,000, and the counts sum to 4, matching the source.
For a product breakdown within each area, include both Area and Product under Group by. Leaving grouping empty instead produces one overall summary. To show the largest total first, reopen Refine results, add a descending Sort on Total amount after the summary, then Apply.
Are blank amounts included in the count?
Count includes records with blank amounts. Sum and Average use values that can be read as numbers. If the count looks right but the total is low, inspect blank amounts and check whether amounts stored as text converted correctly.
The summary shows your grouping fields and totals. Keep the source file available when you need to look up an individual transaction or note. Excel PivotTables and SUMIF also suit this task; use the method that fits how you maintain your reports.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
Discussion about summing numbers for repeated values — Reddit r/excel
The area records and amounts are fictional examples of the grouping problem above.
