Cellwise Start free

Excel guide · 4 min read

How to calculate units per transaction in Excel

Quick answerUnits per transaction (UPT) is total units sold divided by the number of transactions. From a POS export with one row per item, the units are a simple SUMIFS, but the transactions must be counted as unique receipt numbers, not rows: =SUMIFS(D2:D12,A2:A12,"Downtown")/COUNTA(UNIQUE(FILTER(B2:B12,(A2:A12="Downtown")*(D2:D12>0)))) in Excel 365.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGH
1StoreReceiptItemUnitsAmountSale receiptTarget UPT
2DowntownR1001Socks32412.5
3DowntownR1001T-shirt1180
4DowntownR1002Jeans1651
5DowntownR1003T-shirt2361
6DowntownR1003Belt1300
7DowntownR1003Socks180
8DowntownR1004Jeans-1-650
9MallR2001Hoodie1551
10MallR2001Socks2160
11MallR2002T-shirt1181
12MallR2003Cap1221

Download this sheet as an Excel practice file (.xlsx)

What the POS export looks like

Most point-of-sale systems export one row per item scanned, not one row per sale. In the sheet above, receipt R1003 has three rows (two T-shirts, a belt, a pair of socks: four units), and R1004 is a return of jeans, so its units and amount are negative.

That structure is the whole difficulty. Units are easy to add up. Transactions are not the number of rows.

The trap: dividing by the number of rows

COUNTIFS on the store column counts item lines. Downtown has 7 lines but only 4 receipts, so this divides by far too much.

=SUMIFS(D2:D12,A2:A12,"Downtown")/COUNTIFS(A2:A12,"Downtown")

Result: 1.1428571429

1.14, which would mean most customers buy one item. Wrong: it's units per line, not per transaction.

Method 1: Count unique receipts (Excel 365)

UNIQUE removes repeated receipt numbers and COUNTA counts what's left. FILTER keeps only the store you want and only lines with positive units, so a receipt that is just a return doesn't count as a sale.

Then divide net units by that count. Net units here are 8: nine units sold and one returned.

=SUMIFS(D2:D12,A2:A12,"Downtown")/COUNTA(UNIQUE(FILTER(B2:B12,(A2:A12="Downtown")*(D2:D12>0))))

Result: 2.6666666667 shows as 2.67 with Number format

2.67: 8 net units over 3 sale transactions (R1001, R1002, R1003).

Method 2: A first-receipt helper column (any version)

Without UNIQUE, mark the first row of each receipt with a 1. Column F above does that, copied down from row 2: =IF(AND(COUNTIF(B$2:B2,B2)=1,SUMIFS($D$2:$D$12,$B$2:$B$12,B2)>0),1,0). The expanding range B$2:B2 makes COUNTIF return 1 only the first time a receipt appears, and the SUMIFS part skips receipts whose net units aren't positive. Summing that column per store gives the transaction count, and it works in a pivot table too.

=SUMIFS(D2:D12,A2:A12,"Mall")/SUMIFS(F2:F12,A2:A12,"Mall")

Result: 1.6666666667 shows as 1.67 with Number format

1.67 for Mall: 5 units over 3 transactions. Downtown gives the same 2.67 as method 1.

Every store at once, and the PivotTable catch

With the helper column in F, one Excel 365 formula builds the whole store table: UNIQUE lists each store once, and SUMIFS accepts that list as its criteria, so it returns one units total and one transaction count per store. Add a store to the export and it appears in the table. For the whole company, divide the totals, not the store results: =SUM(D2:D12)/SUM(F2:F12) is 13 units over 6 sale transactions, about 2.17.

A PivotTable is the other way to get this every week, and it has a trap. Drag Receipt into Values and it shows Count of Receipt, which counts rows: 7 for Downtown, the same wrong number as the COUNTIFS above. The fix is Distinct Count, and it only appears when the data is in the Data Model.

  1. Click inside the export and choose Insert > PivotTable.
  2. In the dialog, tick Add this data to the Data Model, then OK.
  3. Put Store in Rows and Receipt in Values.
  4. Click Count of Receipt > Value Field Settings, choose Distinct Count, then OK. Note that this counts the return-only receipt R1004 too, so for the net rule above, sum the Sale receipt column (F) instead.
=LET(s,UNIQUE(A2:A12),HSTACK(s,ROUND(SUMIFS(D2:D12,A2:A12,s)/SUMIFS(F2:F12,A2:A12,s),2)))

Result: spills 2 rows × 2 columns

Downtown2.67
Mall1.67

Two rows: Downtown 2.67 and Mall 1.67.

Returns: decide the rule and write it down

There's no single standard, so pick one and keep it. The common choice, used above, is net units (returns subtract) over sale transactions (return-only receipts don't count). It measures what customers kept. Some stores report gross units instead, to coach staff on what they rang up at the till.

=SUMIFS(D2:D12,A2:A12,"Downtown",D2:D12,">0")/SUMIFS(F2:F12,A2:A12,"Downtown")

Result: 3

3.00 gross for Downtown, against 2.67 net. The gap is the returned pair of jeans. Comparing the two each week shows whether a high UPT is sticking.

Put UPT next to average transaction value, against a target

UPT on its own can mislead: selling three pairs of socks lifts it more than one coat does. Read it next to average transaction value (net sales / transactions) and average unit price (net sales / net units). Downtown averages $38.67 per transaction and $14.50 per unit; Mall averages $37.00 per transaction.

Keep the target in an input cell (H2) so a manager can change it without editing formulas. To see the gap in units, =ROUNDUP(H2*SUMIFS(F2:F12,A2:A12,"Mall"),0)-SUMIFS(D2:D12,A2:A12,"Mall") says Mall needed 3 more units across its 3 transactions to reach 2.5.

=IF(SUMIFS(D2:D12,A2:A12,"Mall")/SUMIFS(F2:F12,A2:A12,"Mall")>=H2,"On target","Below target")

Result: Below target

Below target: 1.67 against 2.5.

Common mistakes

Counting rows as transactions

A POS export has one row per item. Count unique receipt numbers with UNIQUE, or a first-occurrence helper column.

Counting return receipts as sales

A return-only receipt adds a transaction with negative units and drags UPT down twice. Filter to receipts with positive net units.

Averaging daily UPTs

A quiet Monday and a busy Saturday don't weigh the same. Sum units and transactions for the whole period, then divide.

Receipt numbers that repeat across stores or days

If tills restart numbering, combine store, date and receipt into one key (=A2&"|"&B2) before counting unique values.

FAQ

What is the formula for units per transaction?

UPT = total units sold / number of transactions. With one row per item, count the transactions as unique receipt numbers, not rows.

How do I count unique transactions in Excel?

In Excel 365 or 2021, =COUNTA(UNIQUE(FILTER(receipts,store="Downtown"))). In older versions, add a helper column with =IF(COUNTIF(B$2:B2,B2)=1,1,0) and sum it.

Why doesn't my PivotTable have Distinct Count?

Distinct Count is only offered when the PivotTable is built on the Data Model. Recreate it with Insert > PivotTable and tick Add this data to the Data Model, or add a first-receipt helper column to the source and sum that instead.

Should returns be included in UPT?

Most reports use net units over sale transactions, so returns reduce the units but don't add a transaction. Whatever you choose, apply it the same way every period.

What's the difference between UPT and average order value?

UPT counts items per sale; average order value (or average transaction value) is revenue per sale. They move together but not always: one expensive item is a high value, low UPT sale.

Functions used

Related guides

Updated