Answering the question "what are the sales this month in a specific area" can be done in two ways in Excel: using the SUMIFS formula or a PivotTable. Both are correct. The difference lies in what happens when the data increases, when the question changes, and when someone else opens your file.
The Difference in One Table
| SUMIFS | PivotTable | |
|---|---|---|
| Result when source data changes | Recalculated immediately | Needs to be refreshed |
| New question (e.g., change area) | Write or modify the formula | Drag columns to another area |
| Number of conditions | Up to 127 pairs of ranges and criteria | As many as the available columns |
| Subtotals and totals | Created manually | Created automatically |
| Suitable for | A single number in a specific cell | Summary per category |
SUMIFS: One Number, Always Up-to-Date
The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...). Note the order: the range to be summed is written first. According to Microsoft documentation, in SUMIFS the sum_range argument is first, while in SUMIF it is the third argument, and this is the most common source of confusion.
Some rules to remember:
- Each criteria range must be the same size as the range being summed, in terms of the number of rows and columns.
- Text criteria must be enclosed in quotes. Without them, the result could be 0.
- The question mark (?) matches a single character and the asterisk (*) matches a series of characters. To search for those symbols themselves, start with a tilde (~).
Example: SUMIFS(D2:D500, B2:B500, "Yogyakarta", C2:C500, "March") sums column D for rows where column B is Yogyakarta and column C is March.
PivotTable: Many Numbers at Once
Microsoft describes PivotTable as an interactive way to summarize large amounts of data. Excel automatically creates subtotals and grand totals, and you can sort, filter, and group columns without writing formulas.
Data requirements: structured in columns with one header row, each column must have a unique and non-empty header, and no merged cells. When creating it via Insert, then PivotTable, you choose the location: New Worksheet or Existing Worksheet.
The Most Common Trap: Forgetting to Refresh
If you add data to the source, the PivotTable created from that source needs to be refreshed. To do this, right-click inside the PivotTable and select Refresh, or press Alt+F5. To update all PivotTables in the file, select Refresh All. There is also an option "Refresh data when opening the file" in the Data tab under Options.
Two other things that can make the numbers appear unchanged:
- New data outside the source range. Microsoft recommends using Excel tables or dynamic named ranges as sources, as expanding ranges will be recognized during refresh.
- Format changes. To prevent column widths and cell formats from shifting with each refresh, check "Autofit column widths on update" and "Preserve cell formatting on update" in the Layout & Format tab.
How to Choose
Ask three questions. First, do you need a single number or a summary table? A single number in a report fits well with SUMIFS, while a summary table fits with PivotTable. Second, how often does the question change? If it changes frequently, PivotTable is lighter as it does not require rewriting formulas. Third, who will read it? Formulas are easy to trace per cell, while PivotTables are easy to explore.
If you prefer to see the explanation directly, there is a clip from our class at https://taalenta.id/video/dbe-rumus-excel-panjang-pivot-table-jauh-lebih-cepat comparing long formulas with PivotTable.
Sources
- Microsoft Support, SUMIFS function
- Microsoft Support, Overview of PivotTables and PivotCharts
- Microsoft Support, Create a PivotTable to analyze worksheet data
- Microsoft Support, Refresh PivotTable data