PivotTable or SUMIFS for a monthly Excel report?

Choose around the report you have to deliver

A monthly reporting pack may need department totals in exactly the same cells, with budgets, variances, and comments alongside them. SUMIFS suits that arrangement: each formula answers one question without generating the surrounding layout. A PivotTable suits a different stage of work, when the question is still changing from department to product to salesperson.

Neither tool makes the underlying transactions more accurate. With the same numeric rows, dates, and categories included, their sums should agree. SUMIFS adds values that meet multiple criteria; a PivotTable groups data through its field areas. The practical choice is how much of the report should be generated and how much should remain under your control.

Reporting need	Starting choice	Maintenance work
Explore every department present in the source	PivotTable	Include new source rows and refresh
Keep a prescribed department order and fixed cells	SUMIFS	Maintain the department list and date criteria
Switch between department and product views	PivotTable	Rearrange fields
Populate a fixed form from an existing pivot	GETPIVOTDATA	Manage the pivot’s filters as well as its refresh

Start both methods from the same small dataset

Work through this example in desktop Excel, using illustrative transactions with amounts expressed in generic units. It is deliberately small enough to total by hand. Enter dates as real Excel dates and amounts as numbers, then select the headers and data and choose Insert → Table. Give the table the name Sales.

Date	Department	Amount
2026-09-03	Sales	100
2026-09-18	Sales	150
2026-09-20	Support	80
2026-10-02	Sales	120
2026-10-21	Support	90

The total is 540. Sales contributes 250 in September and 120 in October; Support contributes 80 and 90. Those are the expected results for both methods. Do not insert a September subtotal into this source table: it would become another numeric row available to be summed.

Using a Table also gives the formulas meaningful references such as Sales[Amount]. Structured references adjust as rows are added to or removed from a table. That only helps when the appended transactions actually belong to the table. A pasted block separated from it by empty rows is not automatically part of the intended source just because it is on the same worksheet.

Build a pivot when the breakdown needs to move

Select a cell in Sales, choose Insert → PivotTable, and place the result on a new worksheet. Move Department to Rows and Amount to Values. The departmental totals should be 370 and 170. If the value field says Count rather than Sum, inspect the numeric source and select Sum in Value Field Settings. Excel may default to Count when it interprets the field as text.

For monthly columns, add Date to Columns, right-click a date, and use Group to select Years and Months. Including the year prevents the presentation from collapsing September across different reporting years. Another workable design is a consistent YearMonth column in the source, used directly as the column field. Whichever design you choose, use the same reporting period when you compare it with a formula.

The payoff appears when a manager asks for another view. Add a Product field to the source, ensure it is included and refreshed, and move Product into Rows. Nesting it under Department gives a departmental product breakdown without writing one SUMIFS formula per combination. Give that pivot its own worksheet so expansion and rearrangement have room; keep commentary and submission cells elsewhere.

Build SUMIFS around the cells in the reporting template

On a separate report sheet, enter Sales in A5 and a real date of September 1, 2026 in B4. Displaying B4 as “Sep” is fine; replacing it with the text “September” is a different input. Put the following formula in B5 to return 250.

=SUMIFS(Sales[Amount],Sales[Department],$A5,Sales[Date],">="&B$4,Sales[Date],"<"&EDATE(B$4,1))

The first argument identifies what to add. The remaining pairs require the department to match A5 and the transaction date to fall on or after B4 but before the following month. EDATE moves a date by a specified number of months, so a September 1 starting date produces October 1 as the upper boundary.

This boundary also handles timestamps at the end of a month. A transaction at noon on September 30 is earlier than October 1. It would fail a condition that stopped at midnight at the beginning of September 30. You do not need to work out the final second of each month.

Put Support in A6 and copy the formula down to obtain 80. Put October 1 in C4 and copy and paste B5 into C5 to obtain 120. The dollar signs keep the department in column A and the reporting dates in row 4. Use copy and paste for this example: Microsoft documents that dragging a fully qualified structured reference horizontally with fill can shift its column specifiers. Inspect Amount, Department, and Date after extending the report.

The surrounding cells can now hold budgets and commentary. A department with no qualifying transactions can remain in a predetermined position with a zero total. The cost is maintaining that list yourself. If a new Training department appears in the source next month, SUMIFS does not create a new report row for it. Adding the department to the reporting template is part of maintaining this design.

Make the next month’s update explicit

Append an October Sales transaction for 30 to the Table. October Sales should become 150, and the overall source total should become 570. This is a useful small verification because it checks both inclusion of the new row and updating of the result. A total that stays at 540 points you toward the source boundary or update process before you start rewriting the criteria.

Formulas normally update under automatic calculation. If a SUMIFS result stays unchanged, use Formulas → Calculation Options to inspect the mode, or Calculate Now for a manually calculated workbook. Microsoft notes that changing calculation options in desktop Excel affects all open workbooks. Make the change deliberately when several reporting files are open.

For a pivot, right-click inside it and select Refresh. As checked on October 26, 2026, Microsoft’s refresh documentation lists PivotTable Auto Refresh for local workbook data as available to Microsoft 365 Insider participants. Do not design a colleague’s update process around an option that their installation may not expose. Manual refresh remains a clear step they can perform.

Keep a dated copy of an approved reporting file when later source changes should not revise the submitted result. The editable workbook can continue accumulating transactions, while the saved submission records the period and data used at that point. This also makes it possible to explain why a later corrected transaction changes the current report.

Reconcile a single department before the grand total

A Microsoft community question describes different totals despite apparently identical criteria. Start with one department and one month, then inspect its qualifying source rows. In the example, a September Sales result of 100 immediately directs attention to the second Sales transaction rather than every row in the workbook.

Check whether that row has a real date, the expected department spelling, and a numeric amount, and whether both methods include it. An outdated pivot needs its source and refresh checked. Two matching but excessive totals may instead reflect duplicate source transactions or an embedded subtotal.

If the team already maintains pivots, a fixed report need not duplicate all their aggregation logic. Type an equals sign in a report cell and click the required pivot result; with formula generation enabled, Excel produces GETPIVOTDATA. Its field and item arguments request a meaningful value instead of whatever happens to occupy B7. It still depends on visible pivot data and can return an error when a filter hides the requested item. Keep the reporting pivot’s filters stable if other people use a separate pivot to explore the data.