Excel Count reset 2

[ThomasBrown]

Hi there,

I need to do a count on Column B however I need the count to reset if Column A changes

Column A Column B Count Formula result
1 2 1
1 3 2
1 5 3
2 2 1
2 4 2
3 1 1

I need a formula to get me Count Formula result.

Any assistence in this would be great.

Many Thanks
Thomas

 

[HarleyQuinn]

Something like (starting on the second row, assuming you already have a 1 in the first row...):

=IF(RC[-2]=R[-1]C[-2],R[-1]C+1,1)

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[xlhelp]

Data starts in A2, Formula in C2:
=COUNTIF($A$2:$A2,A2)

A man has only two choices: He can be right or he can be happy.

 

[lionelhill]

... and note the subtle difference between the two solutions. HarleyQuinn's resets the count every time column A changes; xlhelp's counts the total number of entries (so far) with the same value in column A. So if column A contains numbers 1, 1, 2, 2, 3, 1, 1, 4 the two will respond differently.

 

[mintjulep]

=if(A2<>A1,C1+1,1)

Note that column B is irrelevant.

 

[HarleyQuinn]

mintjulep - That's almost the same as mine except mine correctly adds the number if the value in the previous row is the same (here's mine without using the R1C1 reference style):

=IF(A2=A1,C1+1,1)

Regards

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[ThomasBrown]

Awesome, thank you guys, this has sorted it out.

 

[HarleyQuinn]

Glad we could help

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com