Subtotal of consecutive values of the same value

[Hopper69]

Hi .

I am wanting a formula that will count the number of consecutive sames values in a column that are together but not all of them in the column.

I have 365 rows and i want to be able to insert the formula in B1 and drag down to B365 so that the Value in B wil update as and when A changes.



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bob[/TD]
[TD]3[/TD]
[TD]Leave[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]James[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]James[/TD]
[TD]2[/TD]
[TD]Leave[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Bob[/TD]
[TD]4[/TD]
[TD]Leave[/TD]
[/TR]
</tbody>[/TABLE]


The issue i am having is the not including the top 3 "Bob"'s with the bottom 4 Bob's

=IF(C1<>"",COUNTIF($A$1:A1,A1),"")

Drags down to :

=IF(C9<>"",COUNTIF($A$1:A9,A9),"")

With this my B9 number is 7

Thanks in advance.

P

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try
Formula in B1 copied down
=IF(C1<>"",COUNTIF(INDEX(A:A,IFERROR(LOOKUP(2,1/(A$1:A1<>A1),ROW(A$1:A1)),1)):A1,A1),"")

Hope this helps

M.

 

[Hopper69]

Welcome to Mr Excel forum

Try
Formula in B1 copied down
=IF(C1<>"",COUNTIF(INDEX(A:A,IFERROR(LOOKUP(2,1/(A$1:A1<>A1),ROW(A$1:A1)),1)):A1,A1),"")

Hope this helps

M.

Hi Marcelo,

Thank you so much for the very quick response, really appreciated.

I tested your formula in a table as a i described and it it worked perfect. I provided a very simplicity example for my posting
I have now modified your formula it to fit in my speadsheet and it doesn't work .

In my spreadsheet
C is actually G
A is actually C

My first row i 6 ( above are headers)



=IF(C6<>"",COUNTIF(INDEX(A:A,IFERROR(LOOKUP(2,1/(A$1:A6<>A6),ROW(A$1:A6)),1)):A6,A6),"")
changed to
=IF(G6<>"",COUNTIF(INDEX(C$6:C$365,IFERROR(LOOKUP(2,1/(C$6:C6<>C6),ROW(C$6:C6)),1)):C6,C6),"")

For some reason the first count works after that it is short by 4 everytime. Any Ideas why ?

P

 

[Marcelo Branco]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Header1​

[/td][td]

Formula​

[/td][td][/td][td][/td][td]

Header3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Bob​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Bob​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Bob​

[/td][td]

3​

[/td][td][/td][td][/td][td]

Leave​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

James​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

James​

[/td][td]

2​

[/td][td][/td][td][/td][td]

Leave​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Bob​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Bob​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

Bob​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

Bob​

[/td][td]

4​

[/td][td][/td][td][/td][td]

Leave​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

Mark​

[/td][td]

1​

[/td][td][/td][td][/td][td]

Leave​

[/td][/tr]
[/table]

Formula in D6 copied down
=IF(G6<>"",COUNTIF(INDEX(C$6:C$365,IFERROR(LOOKUP(2,1/(C$6:C6<>C6),ROW(C$6:C6)-ROW(C$6)+1),1)):C6,C6),"")

M.

 

[Hopper69]

Try


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Header1​

[/TD]
[TD]

Formula​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Header3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Bob​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Bob​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Bob​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Leave​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

James​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

James​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Leave​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Bob​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Bob​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Bob​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

Bob​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Leave​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

Mark​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Leave​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in D6 copied down
=IF(G6<>"",COUNTIF(INDEX(C$6:C$365,IFERROR(LOOKUP(2,1/(C$6:C6<>C6),ROW(C$6:C6)-ROW(C$6)+1),1)):C6,C6),"")

M.

Fantastic,

I will next take the time to workout your formula to see what each bit does,

Thanks again.

All the best

Peter

 

[Marcelo Branco]

Peter,

You're welcome. Thanks for the feedback.

M.