Countif problem?

J

Jan Kronsell

I have the following numbers in my A-columnn:

34498
34413
34413
34487
34487
34487
34413
34487
34475

Is there any way, using a formula, that iIcan count the number of changes.
For instance 34498 to 34413 is one change- 34413 to 34487 is another change.
34487 to 34413 is yet another change and so on. I can do it using VBA, but
can it be done using a formula.

Jan
 
S

Sandy

What about using a helper column say B

=IF(A1=A2,"","Change")

and drag it down.
Count the no of times change appears.
Sandy
 
M

MartinW

Hi Jan,

You could use a helper column, in your example it would be column B.
Put this in B2 =IF(A2<>A1,1,"")
and drag down as far as is needed and then sum column B.

That may be a bit clumsier than what you are looking for, however,
it is a perfectly valid way to use this program.

HTH
Martin
 
J

Jan Kronsell

This is what I have already done, but thank you anyway. I was looking for a
formula, that could do it in one calculation. And you inspred me anyway :)

=SUM(IF(A2:A9<>A1:A8,1,0))

entered as an array formula does the trick so thank you.

Jan
 
Top