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.

	A	B	C
1	Bob
2	Bob
3	Bob	3	Leave
4	James
5	James	2	Leave
6	Bob
7	Bob
8	Bob
9	Bob	4	Leave

<tbody>
</tbody>

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

	C​	D​	E​	F​	G​
5​	Header1​	Formula​			Header3​
6​	Bob​
7​	Bob​
8​	Bob​	3​			Leave​
9​	James​
10​	James​	2​			Leave​
11​	Bob​
12​	Bob​
13​	Bob​
14​	Bob​	4​			Leave​
15​	Mark​	1​			Leave​

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

	C​	D​	E​	F​	G​
5​	Header1​	Formula​			Header3​
6​	Bob​
7​	Bob​
8​	Bob​	3​			Leave​
9​	James​
10​	James​	2​			Leave​
11​	Bob​
12​	Bob​
13​	Bob​
14​	Bob​	4​			Leave​
15​	Mark​	1​			Leave​

<tbody>
</tbody>

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.