How to Build a Summary Table in Excel With SUMIFS
· 8 min read
A summary table turns many raw rows into a few totals: one row per category, with a count, a sum and a share of the total. You can build one with a pivot table, or with formulas that sit next to the data and update the moment a row changes. The formula version has three parts:
- A label column with each category typed once.
- A COUNTIF or SUMIFS formula next to the first label that looks the label up in the raw data, filled down.
- A total row and a % of total column, with the total locked by
$signs.
Add a second condition on the column headers and the same idea becomes a two-way grid. This guide builds both on one small shipments log, a typical summary table Excel task, and shows the check that catches a broken grid.
The raw data
Twelve shipments, each with a carrier, the warehouse it left from and the freight cost. The summary on the right answers "how many shipments did each carrier take, and what did they cost?"
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Shipment | Carrier | Warehouse | Cost | Carrier | Shipments | Total cost | Share of cost | Avg cost | |
| 2 | S-01 | SwiftFreight | Reno | $120.00 | SwiftFreight | 4 | $540.00 | 46.6% | $135.00 | |
| 3 | S-02 | Northline | Reno | $95.00 | Northline | 3 | $305.00 | 26.3% | $101.67 | |
| 4 | S-03 | SwiftFreight | Dayton | $140.00 | ParcelPro | 5 | $315.00 | 27.2% | $63.00 | |
| 5 | S-04 | ParcelPro | Reno | $60.00 | Total | 12 | $1,160.00 | 100.0% | ||
| 6 | S-05 | Northline | Dayton | $110.00 | ||||||
| 7 | S-06 | SwiftFreight | Reno | $130.00 | ||||||
| 8 | S-07 | ParcelPro | Dayton | $75.00 | ||||||
| 9 | S-08 | ParcelPro | Reno | $45.00 | ||||||
| 10 | S-09 | ParcelPro | Reno | $65.00 | ||||||
| 11 | S-10 | SwiftFreight | Dayton | $150.00 | ||||||
| 12 | S-11 | Northline | Dayton | $100.00 | ||||||
| 13 | S-12 | ParcelPro | Dayton | $70.00 |
Step 1: the label column
Type each carrier once in F2:F4. This list decides what the summary shows, so spell the labels exactly as they appear in the data. "Parcel Pro" with a space would count zero shipments, and nothing would tell you why.
In Microsoft 365 you can let the sheet build the list for you with =SORT(UNIQUE(B2:B13)), which spills one row per carrier and grows when a new carrier appears. In older versions, copy the column and use Remove Duplicates.
Step 2: count and sum per label
The count in G2:
=COUNTIF($B$2:$B$13, F2)The total cost in H2:
=SUMIFS($D$2:$D$13, $B$2:$B$13, F2)Fill both down to row 4. The results:
| Carrier | Shipments | Total cost |
|---|---|---|
| SwiftFreight | 4 | 540 |
| Northline | 3 | 305 |
| ParcelPro | 5 | 315 |
The data ranges are locked with $ because every row of the summary must read the same twelve shipments. F2 is not locked, because each row should look up its own carrier. If you lock F2 as well, every row shows SwiftFreight's numbers. That is the most common way a summary table goes wrong.
SUMIFS works with one condition, so you can keep the same function when you add a second one later. SUMIF would also work here. Both are covered in SUMIFS with multiple criteria, and the counting side in COUNTIF in Excel.
Average per label
Column J divides the total by the count: =H2/G2. SwiftFreight averages 135.00 per shipment, Northline 101.67 and ParcelPro 63.00. AVERAGEIFS gives the same answer in one step. Dividing the two columns you already have is easier to check, and if a carrier has no shipments it shows #DIV/0! rather than hiding the gap.
Step 3: total row and % of total
Row 5 adds up each column with SUM. The share in I2 divides the carrier's cost by the locked total:
=H2/$H$5Formatted as a percentage, the shares are 46.6%, 26.3% and 27.2%, and I5 shows 100.0%. Always keep that 100% cell. If the shares do not add up to 100%, a range has slid or the total includes something it should not. Percentage of total in Excel goes through the $ signs and the usual mistakes in detail.
The check that catches missing rows
The total in H5 should equal the plain sum of the raw cost column: =SUM(D2:D13), which is also 1,160. If the summary total is lower, some rows have a carrier that is not in your label list, often a typo or a trailing space. Comparing the two numbers takes one formula and catches the problem before anyone reads the report.
A two-way summary grid
Now split each carrier's cost by warehouse. The carriers stay down the side, the warehouses go across the top, and one formula fills the whole grid:
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Shipment | Carrier | Warehouse | Cost | Carrier | Reno | Dayton | Total | |
| 2 | S-01 | SwiftFreight | Reno | $120.00 | SwiftFreight | $250.00 | $290.00 | $540.00 | |
| 3 | S-02 | Northline | Reno | $95.00 | Northline | $95.00 | $210.00 | $305.00 | |
| 4 | S-03 | SwiftFreight | Dayton | $140.00 | ParcelPro | $170.00 | $145.00 | $315.00 | |
| 5 | S-04 | ParcelPro | Reno | $60.00 | Total | $515.00 | $645.00 | $1,160.00 | |
| 6 | S-05 | Northline | Dayton | $110.00 | Matches raw total | TRUE | |||
| 7 | S-06 | SwiftFreight | Reno | $130.00 | AI's grid | Reno | Dayton | ||
| 8 | S-07 | ParcelPro | Dayton | $75.00 | SwiftFreight | $250.00 | $0.00 | ||
| 9 | S-08 | ParcelPro | Reno | $45.00 | Northline | $0.00 | $0.00 | ||
| 10 | S-09 | ParcelPro | Reno | $65.00 | ParcelPro | $0.00 | $0.00 | ||
| 11 | S-10 | SwiftFreight | Dayton | $150.00 | |||||
| 12 | S-11 | Northline | Dayton | $100.00 | |||||
| 13 | S-12 | ParcelPro | Dayton | $70.00 |
The formula in G2:
=SUMIFS($D$2:$D$13, $B$2:$B$13, $F2, $C$2:$C$13, G$1)Mixed references do the work
The two label references each lock one thing:
| Reference | Locked | Why |
|---|---|---|
$F2 | the column | Filled right, it must keep reading the carrier names in F. Filled down, it moves to the next carrier. |
G$1 | the row | Filled down, it must keep reading the warehouse names in row 1. Filled right, it moves to the next warehouse. |
$D$2:$D$13 and the other data ranges | both | Every cell reads the same shipments. |
Write the formula once in G2, fill it right to H2, then fill G2:H2 down to row 4. The grid:
| Carrier | Reno | Dayton | Total |
|---|---|---|---|
| SwiftFreight | 250 | 290 | 540 |
| Northline | 95 | 210 | 305 |
| ParcelPro | 170 | 145 | 315 |
| Total | 515 | 645 | 1,160 |
The carrier totals in column I match the one-way summary, and the grand total matches the raw data. G6 checks that with =I5=SUM(D2:D13) and shows TRUE. If you are new to mixed references, absolute references in Excel explains $F2 and G$1 step by step.
When the $ signs are missing
The AI's grid in F7:H10 uses the same SUMIFS without $ on the labels. Only the top-left cell is right (250). Filled right, F8 became G8, so the carrier criterion is now the number 250. Filled down, G7 became G8, so the warehouse criterion is a number too. Every other cell shows 0.
Zeros are the friendly version of this bug, because you can see them. The unfriendly version locks too much: with $F$2 instead of $F2, every row repeats SwiftFreight's numbers and the grid looks full and reasonable. The check is the same in both cases: the grid total must match the raw total, and each row should differ from the one above unless the data really says so.
Share of total inside a grid
For "what share of each warehouse's cost went to each carrier", divide each cell by its column total and lock only the row: =G2/G$5. Filled across and down, each column adds up to 100%. For each carrier's split between warehouses, lock the column instead: =G2/$I2.
Formula table or pivot table?
A pivot table builds the same summary in a few clicks, and its Show Values As menu gives % of grand total, % of column and % of row without any formulas. It is the faster choice for exploring data.
A formula summary is better when:
- The layout is fixed, such as a monthly report with the same rows every time.
- Other formulas need to point at the results. Pivot tables move when the data changes.
- The numbers must update without anyone remembering to press Refresh.
- You want to see exactly which rows went into each number.
Many analysts explore with a pivot table and then build the final report with SUMIFS. For dates, add two conditions on the same date column: on or after the first day of the month, and on or before its last day, which EOMONTH gives you.
You can practise building summaries like these on a full year of order data on the practice page.
FAQ
How do I make a summary table in Excel without a pivot table?
List each category once, then use =SUMIFS(values, category_range, label) next to the first label and fill it down. Lock the data ranges with $ and leave the label reference unlocked.
How do I make a summary table with two criteria?
Put one set of labels down the side and the other across the top, then use a SUMIFS with both: =SUMIFS($D$2:$D$13, $B$2:$B$13, $F2, $C$2:$C$13, G$1). Fill it across and down.
Why does every row of my summary show the same number?
The label reference is locked, for example $F$2, so every row looks up the same category. Use $F2 (or plain F2 in a one-way table).
How do I add a percentage of total column?
Divide each row's total by the grand total with the grand total locked: =H2/$H$5. The column should add up to 100%.
Is a pivot table better than SUMIFS?
For quick exploration, usually yes. For a fixed report that other formulas depend on, SUMIFS is easier to control and always up to date.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- A region summary without a pivot table
- Region by channel, in one formula
- Every region has the same order countsAudit
- Each category’s share of 2025 revenue