How to count cell that contain consecutive number from 0 to 2 in array

Ferd

New Member
Joined
Jul 9, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I need help on excel formula that can count how many cell in between 0 to 2 number in sequential and reset again the count once reach to the cell contain 0 and start again to count once reach to cell contain number 2.
Also the formula will IGNORE the count when cell did not start with number 0 and end to number 2 or did not follow the sequential 0,1,2.
Currently i have thousand data like this and i need to calculate the cell manually. Really appreciate if anyone can help.??
 

Attachments

  • Capture.JPG
    Capture.JPG
    101.1 KB · Views: 27

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Welcome to the MrExcel forum!

Try:

Book1
AB
1NumberTotal Cell
204
31 
41 
52 
61 
71 
81 
91 
101 
111 
120 
131 
141 
150 
161 
171 
181 
191 
201 
211 
2206
231 
241 
251 
261 
272 
28 
Sheet5
Cell Formulas
RangeFormula
B2:B28B2=IF(A2=0,IF(ISERROR(MATCH(0,A3:A$100,0)),IFERROR(MATCH(2,A3:A$100,0)+1,""),IFERROR(IF(MATCH(0,A3:A$100,0)>MATCH(2,A3:A$100,0),MATCH(2,A3:A$100,0)+1,""),"")),"")
 
Upvote 0
Eric

Nice!
But I think your formula needs a little tweaking. Change the value in A13 to 2 and see what happens

To fix it, just change the first condition to
=IF(AND(A2=0,A3=1),......)

M.
 
Upvote 0
Solution
But I think your formula needs a little tweaking. Change the value in A13 to 2 and see what happens


Oh, good catch! I didn't consider that case. I tried a few variations, including one with FREQUENCY and one with FILTER, but I couldn't find anything significantly better or shorter.
 
Upvote 0
Hello Eric and Marcelo , thanks a lot, your guy are really my saviour!! .

BTW i had problem when enter 2nd formula from Marcelo. It didnt calculate anything. The 1st formula is good, but it will count if have 2 after 0 that didnt meet the criteria. I had try to add Marcelo additional condition, but it seem didnt count nothing.

Maybe I had put it in wrong way, could you help to check below :
=IF(AND(A2=0,A2=1),IF(ISERROR(MATCH(0,A3:A$100,0)),IFERROR(MATCH(2,A3:A$100,0)+1,""),IFERROR(IF(MATCH(0,A3:A$100,0)>MATCH(2,A3:A$100,0),MATCH(2,A3:A$100,0)+1,""),"")),"")
 
Upvote 0
Hello Eric and Marcelo , thanks a lot, your guy are really my saviour!! .

BTW i had problem when enter 2nd formula from Marcelo. It didnt calculate anything. The 1st formula is good, but it will count if have 2 after 0 that didnt meet the criteria. I had try to add Marcelo additional condition, but it seem didnt count nothing.

Maybe I had put it in wrong way, could you help to check below :
=IF(AND(A2=0,A2=1),IF(ISERROR(MATCH(0,A3:A$100,0)),IFERROR(MATCH(2,A3:A$100,0)+1,""),IFERROR(IF(MATCH(0,A3:A$100,0)>MATCH(2,A3:A$100,0),MATCH(2,A3:A$100,0)+1,""),"")),"")
 
Upvote 0
Thank you very much Eric and Marcelo, i just found the error, it cause by 2nd condition i put it same row "A2".

Thank you very much guys. I had been 3 day to figure out of this formula, but it didnt come out.

Your guys are really smart!!
I need to learn more.
Thanks again!
 
Upvote 0
=IF(AND(A2=0,A3=1),IF(ISERROR(MATCH(0,A3:A$100,0)),IFERROR(MATCH(2,A3:A$100,0)+1,""),IFERROR(IF(MATCH(0,A3:A$100,0)>MATCH(2,A3:A$100,0),MATCH(2,A3:A$100,0)+1,""),"")),"")

Is what @Marcelo Branco was saying for the formula in B2
 
Upvote 0
A slightly different approach that appears to work, although I have only done a quick test.
Book1
AB
1NumberTotal Cell
20 
31 
41 
524
61 
71 
81 
91 
101 
111 
120 
132 
141 
150 
161 
171 
181 
191 
201 
211 
220 
231 
241 
251 
261 
2726
Sheet1
Cell Formulas
RangeFormula
B2:B27B2=IFERROR(IF(AND(A2=2,A1=1),LET(r,A1:INDEX(A$1:A1,XMATCH(0,A$1:A1,,-1)+1),IF(COUNTIF(r,"<>1"),"",ROWS(r)+2)),""),"")
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top