How to Consolidate Purchase Orders in Excel

A purchase order arrives as several rows, one per item, and you need one row per PO with the full total beside it. The formulas take ten minutes. The PO number column is what decides whether the answer is right.

The question turns up on every Excel forum in the same words: purchase orders broken out by item, several rows each, and what is wanted is one row per PO with the full total in the next cell. The formulas are short. This covers them quickly, and then covers the PO number column properly, because that is where the wrong answer comes from.

What the export actually looks like

Purchasing systems export at line grain. One row per item, with the PO number repeated:

PO Number   Vendor      Item                 Qty   Line Amount
PO-1042     Acme Ltd    Steel bracket         40      1,240.00
PO-1042     Acme Ltd    Mounting plate        40        860.00
PO-1042     Acme Ltd    Freight                1        145.00
PO-1043     Borealis    Cable, 4mm           500        612.50
PO-1044     Acme Ltd    Steel bracket         12        372.00

Three rows for PO-1042, one each for the others. Select any cell and press Ctrl + T to make it a Table, then rename it PO in the Table Design tab. Everything below refers to it by name, and a Table keeps working when next month is pasted underneath. A fixed range like A2:E500 does not, which is how a working summary quietly stops including the newest orders.

1. The list of distinct POs

On Microsoft 365 or Excel 2021, one formula spills the whole list:

=UNIQUE(PO[PO Number])

Put it in H2 and the PO numbers fill down as far as they need to. On Excel 2019 or earlier there is no UNIQUE: copy the PO column to a spare area, then Data → Remove Duplicates. It is a manual step you have to redo when the data changes, which is the main reason the dynamic-array version is worth the upgrade.

2. The total for each PO

Beside the spilled list, in I2:

=SUMIFS(PO[Line Amount],PO[PO Number],$H2)

Fill down. That is the whole of the original question — the PO in one cell, the full total in another.

If you want it to spill down automatically alongside UNIQUE rather than being filled by hand, wrap the criteria in the spill reference:

=SUMIFS(PO[Line Amount],PO[PO Number],H2#)

The # means "however many rows that formula produced", so the summary grows and shrinks with the data on its own.

3. The item descriptions, joined into one cell

This is the half most guides leave out, and it is usually what the person asking actually wants:

=TEXTJOIN(", ",TRUE,FILTER(PO[Item],PO[PO Number]=$H2))

FILTER returns just the items belonging to that PO; TEXTJOIN glues them together with a comma. The TRUE tells it to skip blanks. For PO-1042 above you get Steel bracket, Mounting plate, Freight in one cell.

Without dynamic arrays this is an array formula and needs confirming with Ctrl + Shift + Enter:

=TEXTJOIN(", ",TRUE,IF(PO[PO Number]=$H2,PO[Item],""))

A caution worth knowing before you build a report on it: TEXTJOIN returns a #VALUE! error if the joined string goes over 32,767 characters. On item descriptions you will never see it. On joined free-text notes you might.

4. The PivotTable version

For the numbers alone, a PivotTable is faster than any of the above and needs no formulas. Select the Table, Insert → PivotTable, put PO Number in Rows and Line Amount in Values. Thirty seconds, one row per PO, correct total.

What it will not do is the descriptions. A PivotTable's value field aggregates numbers — sum, count, average — and there is no text-joining option, so the closest you get is a count of lines. That single limitation is what decides between methods more often than anything else.

5. Power Query, if this happens every month

If the export lands on your desk on a schedule, do it once in Power Query and press refresh thereafter. Data → From Table/Range, then Group By: group on PO Number, add an aggregation of Sum over Line Amount, and add a second one set to All Rows. That second column holds the underlying rows, and a custom column of Text.Combine([AllRows][Item], ", ") turns them into the joined description.

It is more setup than a formula and it is the only version where next month costs nothing.

Which one to actually use

In order, and the order matters more than the individual methods:

1. Power Query if the file arrives regularly from a system. Refreshable, handles the text join, and no formula to re-point when a column moves.
2. UNIQUE + SUMIFS + TEXTJOIN if you are on Microsoft 365 and want a live sheet that updates as rows are added. This is what I would build for a one-off that might become a habit.
3. PivotTable for a one-off where you only need the numbers. Fastest path to a correct total, and you should not talk yourself out of it because it feels less clever.
4. SUMIF against a hand-made PO list only when you already have the list from somewhere else. It is the most common answer on forums and the one that goes stale first, because the list does not maintain itself.

The trap: the PO number column

Every method above matches on the PO number, so every method above is wrong in the same way if that column is not what you think it is. Three failures, in rising order of how long they take to find.

Trailing spaces. Purchasing exports pad. "PO-1042 " and "PO-1042" are different strings, SUMIFS matches neither to the other, and the total comes back as 0. Zero is the merciful version: you notice it. Add a helper column of =TRIM([@[PO Number]]) and match on that.

Text and numbers mixed in one column. If some POs were typed as 1042 and others imported as text "1042", the column looks identical on screen while behaving as two different things. Sort by it and watch the alignment: numbers align right, text aligns left, and a column doing both is the diagnosis. SUMIFS coerces text criteria that look numeric, so it will often match across the two — which sounds like a rescue and is not, because it is inconsistent about when it does.

The 15-digit limit. This is the one that matters, because nothing about it looks wrong. SUMIF, SUMIFS, COUNTIF and COUNTIFS compare purely numeric criteria as numbers, and Excel numbers carry only 15 significant digits. If your PO or requisition numbers are 16 digits or longer — SAP and several ERP exports produce exactly this — then 1000000000000001 and 1000000000000002 are compared as equal. Two distinct orders are totalled as one. There is no error, no warning, and the grand total still foots, because nothing was lost: it was merged.

The direction of the error is worth stating plainly. Trailing spaces and type mismatches understate a PO, usually all the way to zero, and somebody spots a zero. The digit limit overstates one PO and drops another out of the list entirely, and the column still adds up to the right grand total. It survives review for exactly that reason.

Where the codes are that long, stop using SUMIFS for the match:

=SUMPRODUCT(--EXACT(PO[PO Number],$H2),PO[Line Amount])

EXACT compares as text, character by character, with no numeric conversion and no wildcard behaviour. It is slower on large sheets and it is correct.

Where a consolidation stops helping

One row per PO answers one question well and the next one badly. As soon as somebody asks which vendor took the most orders last quarter, or how the average PO value moved over the year, you are back at the line detail building a second summary — and then a third, because the question after that is different again.

That is the normal life of this file, and it is the reason to keep the line-level export intact rather than replacing it with the tidy version. Every summary is disposable. The rows underneath are not.

Asking for it instead

If the answer changes shape every month, the alternative is to query the export rather than reshape it. Quiriz reads the same PO file and answers in plain English, so total by PO number, spend by vendor last quarter and average PO value by month are three questions rather than three rebuilds. In Excel that is =QUIRIZ.ASK("total line amount by PO number", "table"), spilled straight into the sheet.

The same limit applies to us as to your workbook, and it is the one from the field note above rather than a technical one: we can only tell you what the file says. A PO export carries commitments. If the invoiced figures live in your accounting system, the committed-versus-invoiced comparison needs that export too, and no tool can infer the second number from the first.

Frequently asked questions