Counting cells when Values are Zero or None

[TheTiredEngineer]

So Ive got a column of data like below and I need it to skip & recount if theres any "None" or zeros in column A. Id prefer this to be accomplished via formulas. I tried countif and its not working right.

23	1
None
12	2
12	3
55	4
None
25	5

Sometimes the data can look like this, where the first is a zero and theres a few other zeros.

None
25	1
12	2
12	3
None
25	4
None

 

[dave3009]

Hi

Do you mean something like this?

Book1
	A	B
1
2	23	1
3	None
4	12	2
5	12	3
6	55	4
7	None
8	25	5
Sheet2

 

[TheTiredEngineer]

Hi

Do you mean something like this?

Book1
	A	B
1
2	23	1
3	None
4	12	2
5	12	3
6	55	4
7	None
8	25	5
Sheet2

Yes, the count is always going to change depending on which cells in the column have numbers and which are zero or "none". You could have 1 or you could have 9, it just depends.

 

[dave3009]

Book1
	A	B	C
1
2	23	1
3	None
4	12	2
5	0
6	55	3
7	None
8	25	4
9
Sheet2

Cell Formulas
Range	Formula
B2:B8	B2	=IF((A2=0)+(ISNUMBER(SEARCH("none",A2))), "", MAX($B$1:$B1)+1)

 

[hagia_sofia]

Another option:

=LET(
a,A1:A7,
IF((a<>"None")*(a<>0),SCAN(0,a,LAMBDA(x,y,IF((y<>0)*(y<>"None"),x+1,x))),""))

 

[Tom.Jones]

dave3009, short formula:

=IF((A2=0)+(A2="none"),"",MAX($B$1:$B1)+1)
or
=IF(OR(A2={0,"none"}),"",MAX($B$1:$B1)+1)

 

[TheTiredEngineer]

dave3009, short formula:

=IF((A2=0)+(A2="none"),"",MAX($B$1:$B1)+1)
or
=IF(OR(A2={0,"none"}),"",MAX($B$1:$B1)+1)

This worked great! Thank You!