# How to count how many times a value changes in a column in Excel

Compare each cell with the one above it, using two ranges offset by a row: `=SUMPRODUCT(--(B3:B13<>B2:B12))`.

**Excel:** `=SUMPRODUCT(--(B3:B13<>B2:B12))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Date | Price |  | Price changes |
| 2026-03-01 | 19.99 |  |  |
| 2026-03-02 | 19.99 |  |  |
| 2026-03-03 | 21.49 |  |  |
| 2026-03-04 | 21.49 |  |  |
| 2026-03-05 | 21.49 |  |  |
| 2026-03-06 | 20.99 |  |  |
| 2026-03-07 | 20.99 |  |  |
| 2026-03-08 | 22.99 |  |  |
| 2026-03-09 | 22.99 |  |  |
| 2026-03-10 | 22.99 |  |  |
| 2026-03-11 | 22.99 |  |  |
| 2026-03-12 | 19.99 |  |  |

Formula in E1: `=SUMPRODUCT(--(B3:B13<>B2:B12))`

Result: 4

## How it works

`B3:B13` and `B2:B12` are the same column shifted by one row, so comparing them pairs each day with the day before. Each TRUE is a change, and SUMPRODUCT counts them: the price moved 4 times in 12 days.

The two ranges must be the same size: eleven pairs for twelve days.

## Other ways

- `=SUMPRODUCT(--(B3:B13>B2:B12))`: Counts only increases. Use `<` for decreases. (Counts the 2 price increases.)

## Common mistakes

- Starting both ranges on the same row (`B2:B13<>B2:B13`) compares each cell with itself and returns 0.
- Text comparisons ignore case, so "Open" to "open" is not a change. Use EXACT for case-sensitive changes: `=SUMPRODUCT(--NOT(EXACT(B3:B13,B2:B12)))`.

## Questions

**How do I mark the rows where the value changed?**

In C3, use `=B3<>B2` and fill down.

**How do I count status changes per ticket?**

Sort by ticket and date, then add a same-ticket test: `=SUMPRODUCT((A3:A13=A2:A12)*(B3:B13<>B2:B12))`.

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-how-many-times-a-value-changes