How to count how many times a value changes in a column in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(B3:B13<>B2:B12))Compare each cell with the one above it, using two ranges offset by a row: =SUMPRODUCT(--(B3:B13<>B2:B12)).
- Result on the example
- 4 in
E1 - Works in
- Every current version
- 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
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.
=SUMPRODUCT(--(B3:B13<>B2:B12))Result4
B3:B1311 rows × 1 col: $19.99, $21.49, $21.49, $21.49, …A range: 11 cells in one column.
B2:B1211 rows × 1 col: $19.99, $19.99, $21.49, $21.49, …A range: 11 cells in one column.
B3:B13<>B2:B12{11 rows × 1 col} FALSE, TRUE, FALSE, FALSE, …The <> operator tests whether they differ.
-(B3:B13<>B2:B12{11 rows × 1 col} 0, -1, 0, 0, …Makes the value negative.
--(B3:B13<>B2:B12{11 rows × 1 col} 0, 1, 0, 0, …Makes the value negative.
SUMPRODUCT(--(B3:B13<>B2:B12))4SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(B3:B13<>B2:B12{11 rows × 1 col} 0, 1, 0, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(B3:B13<>B2:B12)). We ran it in Google Sheets mode on the same data and it returns 4.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(B3:B13>B2:B12)) | 2Different result | 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))).
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 how many times a value changes in a column
E1, count how many times the price in B2:B13 changes from one day to the next.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)).
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.