# Count Changes In An Array Of Data

#### peterhinton

##### Active Member
so I have some data that records grades of a product in B2:B54

I want to count the number of times the grade has changed,
not the number of different grade it has been (unique values)

example may look like:

A A A A B B B B C C C B B B B C C C......and so on
(range of grades = A-F)

This would equal to 4 changes,

any ideas how to construct such a formula ?

Last edited:

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### hurgadion

##### Active Member
Hi,
try maybe this (array formula):
Code:
``=SUM(IF((B3:B55<>"")*(B2:B54<>B3:B55);1;0))``
Best regards

#### steve the fish

##### Well-known Member
Maybe:

=SUMPRODUCT(--(\$B\$2:\$B\$20<>OFFSET(\$B\$2:\$B\$20,1,0)),--(OFFSET(\$B\$2:\$B\$20,1,0)<>""))

#### steve the fish

##### Well-known Member
This caters for any value in B21

=SUMPRODUCT(--(\$B\$2:\$B\$20<>OFFSET(\$B\$2:\$B\$20,1,0)))-(\$B\$20<>\$B\$21)

#### mikerickson

##### MrExcel MVP
Perhaps

=SUMPRODUCT(--(B2:B53<>B3:B54))

#### peterhinton

##### Active Member
Thank You Sir Fish, works perfect,

Thanks for the effort hurgadion, although I could not get this to work, even when changing ; to ,

#### hurgadion

##### Active Member
OK,
I'm sorry... I have to remember about this... I have Local Formulas with ","... My formula works if B54 is empty... My Solution is similar to mikerickson One...

Last edited:

Replies
5
Views
229
Replies
6
Views
272
Replies
2
Views
389
Replies
4
Views
244
Replies
6
Views
645

Threads
1,191,584
Messages
5,987,492
Members
440,097
Latest member
Wint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back