How to use SUMIF in Excel: sum sales by category
Your sales records are listed row by row, but you need a total for each category. How much did office supplies sell? What about tech products? Excel’s SUMIF function adds values that meet one condition. This tutorial demonstrates the method in Cue Office, with formulas and a simple way to check the totals.
The syntax is SUMIF(range, criteria, sum_range). For an Office total, look for Office in the category column and add the sales amounts from the matching rows.
The example has five sales records. Column A contains categories, column C contains sales amounts, and columns E:F hold the summary. Adjust these references to fit your own sheet.
The screenshots show Cue Office’s English interface. Keep the sample labels and formulas as shown when following this example.
Sum sales by category with SUMIF in Cue Office
1. Set up the summary area
Enter Category in E1 and Total sales in F1. Enter Office in E2 and Tech in E3. Leave column D empty to separate the source records from the summary.
2. Enter the SUMIF formulas
Click F2 and make sure you are not editing inside the cell. Copy the two lines below and paste them into the selected cell to fill F2:F3. The first formula totals Office; the second totals Tech.
=SUMIF($A$2:$A$6,E2,$C$2:$C$6) =SUMIF($A$2:$A$6,E3,$C$2:$C$6)
3. Check the totals against the records
Office is 120 + 30 + 50 = 200. Tech is 80 + 150 = 230. Check these five records rather than relying only on the absence of a formula error.
Check your results
Do the totals update automatically?
Change C2 from 120 to 140. Office should increase from 200 to 220, while Tech stays at 230. Change C2 back to 120 after checking to restore the example.
How the three SUMIF arguments work
- $A$2:$A$6 — range: the category cells to check.
- E2 — criteria: the category to match, Office.
- $C$2:$C$6 — sum_range: the sales amounts to add for matching rows.
Only rows with a matching category contribute to the total. The second formula uses E3 (Tech) instead of E2. The dollar signs keep the source ranges fixed when you copy the formula.
Why is my SUMIF total wrong?
- Match the category and amount ranges row for row. Both ranges in this example run from row 2 to row 6.
- A new record in row 7 is outside these fixed ranges. Extend both ranges to include it.
- Keep category text consistent. Extra spaces can prevent an expected match.
- Use numeric amounts. A cell that looks like a number may still contain text.
How do I use this formula in my own spreadsheet?
Find your grouping column and your amount column. Replace the first and third arguments with those ranges, using the same starting and ending rows. The second argument can still reference a category in your summary. For department expenses or customer sales, first decide what to group by and which amounts to add.
Solve it in one click with Cue AI
Open Cue AI in Cue Office, describe the summary you want, and send the request. Cue AI can read the current worksheet and write the category totals directly into the sheet, without asking you to copy the data or write a formula.
Ask: “Total sales by category and put the summary to the right of the table.”
Check the generated totals against the original records. In this example, changing C2 from 120 to 140 also updates the AI-generated Office total to 220; Tech remains 230. The exact layout and formatting may vary.
Beyond this example, you can also ask Cue AI in Cue Office to write formulas, clean data, or create summaries—describe the result you need and let it handle the task in one click.