# How to count unique combinations of two columns in Excel

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

**Excel:** `=ROWS(UNIQUE(A2:B13))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Customer | Product |  | Unique pairs |
| Northwind | Desk |  |  |
| Contoso | Chair |  |  |
| Northwind | Chair |  |  |
| Fabrikam | Desk |  |  |
| Contoso | Desk |  |  |
| Litware | Lamp |  |  |
| Northwind | Desk |  |  |
| Adatum | Chair |  |  |
| Contoso | Chair |  |  |
| Fabrikam | Lamp |  |  |
| Litware | Desk |  |  |
| Adatum | Desk |  |  |

Formula in E1: `=ROWS(UNIQUE(A2:B13))`

Result: 10

## 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.

## Other ways

- `=SUMPRODUCT(1/COUNTIFS(A2:A13,A2:A13,B2:B13,B2:B13))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-unique-combinations-of-two-columns