counter column

AliGW

Banned

Joined

Mar 9, 2014

Messages

3,628

• Apr 19, 2014

• #2

In C1, put 1.

In C2, copied down:

=IF(B2="","",IF(AND(B2<>"",B1=""),1,C1+1))

 

Upvote 0

S

SQUIDD

Well-known Member

Joined

Jan 2, 2009

Messages

2,104

Office Version

1. 2019
2. 2016

Platform

1. Windows

• Apr 19, 2014

• #3

Hi AliGW

Thanks for looking, I have put your formula in as you described.

All I seem to get is a 1 in column C if it finds data in column B??

Dave

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,465

Office Version

1. 365

Platform

1. Windows

• Apr 19, 2014

• #4

SQUIDD said:

Hi

I am trying to create a counter in column C.
Like this the counter counts from 1-8 only if column A has data. It resets if column B has data to 1 and starts over.

	A	B	C	D	E	F	G	H	I
1	2		1
2	3		2
3	6		3
4	1		4
5	4		5
6	1		6
7	4		7
8	2		8
9	99		??
10
11	4	3	1
12
13	4	5	1
14	2		2
15	5		3
16	3		4
17
18	6		5
19

<tbody>
</tbody>

Click to expand...

Because you said "...the counter counts from 1-8 only if column A has data...", your question is not entirely clear as to what should happen if your have nine or more consecutive numbers in Column A with a number in Column B. For example, if there was a value in Column A where I have put my red 99, what should go in Column C where I put the double red question marks... 1 or 9? Assuming the answer is 1, put a 1 in cell C1, then put this formula in cell C2 and copy it down to the end of your data...

=IF(LEN(B2),1,IF(LEN(A2),MOD(LOOKUP(9E+307,C$1:C1,C$1:C1),8)+1,""))


EDIT NOTE: If, on the other hand, it should keep counting past 8, then use this formula instead...

=IF(LEN(B2),1,IF(LEN(A2),LOOKUP(9E+307,C$1:C1,C$1:C1)+1,""))

 

Last edited: Apr 19, 2014

Upvote 0

AliGW

Banned

Joined

Mar 9, 2014

Messages

3,628

• Apr 19, 2014

• #5

Give me five minutes.

 

Upvote 0

AliGW

Banned

Joined

Mar 9, 2014

Messages

3,628

• Apr 19, 2014

• #6

With 1 in C1, put this into C2 and copy down:

=IF(AND(A2="",B2=""),"",IF(AND(A2<>"",B2<>""),1,C1+1))

 

Upvote 0

S

SQUIDD

Well-known Member

Joined

Jan 2, 2009

Messages

2,104

Office Version

1. 2019
2. 2016

Platform

1. Windows

• Apr 19, 2014

• #7

Hi Rick

many thanks.
Yes, in your example above, it should be 1 and not 9.
But if i do put a 99 in my data as you have, cell C11 should now be a 2, this is because B9 has no data.

Dave

 

Upvote 0

S

SQUIDD

Well-known Member

Joined

Jan 2, 2009

Messages

2,104

Office Version

1. 2019
2. 2016

Platform

1. Windows

• Apr 19, 2014

• #8

AliGW

many thanks again

I have done as you suggested, it works correct for the data that i posted, but if you in fact put a 99 where Rick suggested, the counter goes to 9, where it should be 1.

Below is Ricks example and expected results.

cheers

A	B	C	D	E	F	G	H	I
1	2		1
2	3		2
3	6		3
4	1		4
5	4		5
6	1		6
7	4		7
8	2		8
9	99		1
10
11	4	3	2
12
13	4	5	1
14	2		2
15	5		3
16	3		4
17
18	6		5
19

<tbody>
</tbody>

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,465

Office Version

1. 365

Platform

1. Windows

• Apr 19, 2014

• #9

SQUIDD said:

Hi Rick

many thanks.
Yes, in your example above, it should be 1 and not 9.
But if i do put a 99 in my data as you have, cell C11 should now be a 2, this is because B9 has no data.

Click to expand...

Try the first formula that I posted... that is exactly what it does (I did not modify your entire posted chart to show that because I was asking a question I did not know the answer to).

 

Upvote 0

S

SQUIDD

Well-known Member

Joined

Jan 2, 2009

Messages

2,104

Office Version

1. 2019
2. 2016

Platform

1. Windows

• Apr 19, 2014

• #10

Hi Rick

On my original data set, your formula works perfect, but if i do put in 99 as you did(which could happen) C11 become incorrect, displaying a 1 instead of 2.

Dave

 

Upvote 0