How to count unique combinations of two columns in Excel

Q: How do I list the combinations?

Use =UNIQUE(A2:B13) on its own; it spills the pairs.

Q: How do I count how often each combination appears?

Next to the list, use =COUNTIFS(A2:A13,D2,B2:B13,E2) for each pair.

Formula (Excel and Google Sheets)

=ROWS(UNIQUE(A2:B13))

Give UNIQUE both columns and count the rows it returns: =ROWS(UNIQUE(A2:B13)).

Result on the example: 10 in E1
Works in: Excel 2021, 2024 and Microsoft 365
Google Sheets: Same formula

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

How it works

Given two columns, UNIQUE compares whole rows, so "Northwind, Desk" and "Northwind, Chair" are different combinations. It returns each combination once, and ROWS counts how many rows that is.

Two rows repeat an earlier pair (Northwind Desk and Contoso Chair), so 12 orders contain 10 combinations.

=ROWS(UNIQUE(A2:B13))

Result10

A2:B1312 rows × 2 cols: "Northwind", "Desk", "Contoso", "Chair", …
A range: 12 rows × 2 columns.
UNIQUE(A2:B13){10 rows × 2 cols} "Northwind", "Desk", "Contoso", "Chair", …
UNIQUE: Distinct rows of a range. Spills.
- arrayA2:B1312 rows × 2 cols: "Northwind", "Desk", "Contoso", "Chair", …
ROWS(UNIQUE(A2:B13))10
ROWS: Number of rows in a range.
- arrayUNIQUE(A2:B13){10 rows × 2 cols} "Northwind", "Desk", "Contoso", "Chair", …

In Google Sheets

The same formula works in Google Sheets: =ROWS(UNIQUE(A2:B13)). We ran it in Google Sheets mode on the same data and it returns 10.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMPRODUCT(1/COUNTIFS(A2:A13,A2:A13,B2:B13,B2:B13))	10	Works in every Excel version: each combination adds 1/n for each of its n rows, so it adds up to 1.

Common mistakes

COUNTA(UNIQUE(A2:B13)) counts cells, not rows, and returns 20. Use ROWS.
Joining the columns, like A2:A13&B2:B13, can merge different pairs ("AB"+"C" and "A"+"BC"). Add a separator such as &"|"& if you use that approach.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count unique combinations of two columns

In E1, count the different customer-and-product combinations in A2:B13.

Questions

How do I list the combinations?

Use =UNIQUE(A2:B13) on its own; it spills the pairs.

How do I count how often each combination appears?

Next to the list, use =COUNTIFS(A2:A13,D2,B2:B13,E2) for each pair.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.