Counting Occurances

I really need some help for work. I have a column that contains various
numeric values and I want to count the number of times it changes from one
value to another, but there are spaces in between the cells. Heres a simple
example,
8
8
8
15
15
18
17
17
8
3
17
15
So, is there a formula or macro, something that can sum up the number of
times the number changes?
I would really appreciate the help.

Try this entered as an array formula with Ctrl-Shift-Enter

List is from A1 to A12

=SUM(IF(ISNUMBER(A1:A12),IF(A1:A12<>A2:A13,1,0),0))-1

With your data I got an answer of 7 ... is this correct?

HTH

"Rusty" wrote:

> I really need some help for work. I have a column that contains various
> numeric values and I want to count the number of times it changes from one
> value to another, but there are spaces in between the cells. Heres a simple
> example,
> 8
> 8
> 8
> 15
> 15
> 18
> 17
> 17
> 8
> 3
> 17
> 15
> So, is there a formula or macro, something that can sum up the number of
> times the number changes?
> I would really appreciate the help.

Please only post to one NG. Thank you!

"Toppers" wrote:

> Try this entered as an array formula with Ctrl-Shift-Enter
>
> List is from A1 to A12
>
> =SUM(IF(ISNUMBER(A1:A12),IF(A1:A12<>A2:A13,1,0),0))-1
>
> With your data I got an answer of 7 ... is this correct?
>
> HTH
>
> "Rusty" wrote:
>
> > I really need some help for work. I have a column that contains various
> > numeric values and I want to count the number of times it changes from one
> > value to another, but there are spaces in between the cells. Heres a simple
> > example,
> > 8
> > 8
> > 8
> > 15
> > 15
> > 18
> > 17
> > 17
> > 8
> > 3
> > 17
> > 15
> > So, is there a formula or macro, something that can sum up the number of
> > times the number changes?
> > I would really appreciate the help.

=SUMPRODUCT(--((A2:A12)-(A1:A11)<>0))-1

"Rusty" wrote:

> I really need some help for work. I have a column that contains various
> numeric values and I want to count the number of times it changes from one
> value to another, but there are spaces in between the cells. Heres a simple
> example,
> 8
> 8
> 8
> 15
> 15
> 18
> 17
> 17
> 8
> 3
> 17
> 15
> So, is there a formula or macro, something that can sum up the number of
> times the number changes?
> I would really appreciate the help.

Sorry about the multiple posts. How do I get this array to work when there
are spaces in between the rows. Example,
8

9

10

10

11

2


"Toppers" wrote:

> Please only post to one NG. Thank you!
>
> "Toppers" wrote:
>
> > Try this entered as an array formula with Ctrl-Shift-Enter
> >
> > List is from A1 to A12
> >
> > =SUM(IF(ISNUMBER(A1:A12),IF(A1:A12<>A2:A13,1,0),0))-1
> >
> > With your data I got an answer of 7 ... is this correct?
> >
> > HTH
> >
> > "Rusty" wrote:
> >
> > > I really need some help for work. I have a column that contains various
> > > numeric values and I want to count the number of times it changes from one
> > > value to another, but there are spaces in between the cells. Heres a simple
> > > example,
> > > 8
> > > 8
> > > 8
> > > 15
> > > 15
> > > 18
> > > 17
> > > 17
> > > 8
> > > 3
> > > 17
> > > 15
> > > So, is there a formula or macro, something that can sum up the number of
> > > times the number changes?
> > > I would really appreciate the help.

=SUMPRODUCT(--((A3:A13)-(A1:A11)<>0))-1
Works if there are no zeroes.

"Rusty" wrote:

> Sorry about the multiple posts. How do I get this array to work when there
> are spaces in between the rows. Example,
> 8
>
> 9
>
> 10
>
> 10
>
> 11
>
> 2
>
>
> "Toppers" wrote:
>
> > Please only post to one NG. Thank you!
> >
> > "Toppers" wrote:
> >
> > > Try this entered as an array formula with Ctrl-Shift-Enter
> > >
> > > List is from A1 to A12
> > >
> > > =SUM(IF(ISNUMBER(A1:A12),IF(A1:A12<>A2:A13,1,0),0))-1
> > >
> > > With your data I got an answer of 7 ... is this correct?
> > >
> > > HTH
> > >
> > > "Rusty" wrote:
> > >
> > > > I really need some help for work. I have a column that contains various
> > > > numeric values and I want to count the number of times it changes from one
> > > > value to another, but there are spaces in between the cells. Heres a simple
> > > > example,
> > > > 8
> > > > 8
> > > > 8
> > > > 15
> > > > 15
> > > > 18
> > > > 17
> > > > 17
> > > > 8
> > > > 3
> > > > 17
> > > > 15
> > > > So, is there a formula or macro, something that can sum up the number of
> > > > times the number changes?
> > > > I would really appreciate the help.