Find/count consecutive entries in a row in excel

Hi everyone,

I had a rather intriguing excel question for the forum.

I have a row in excel that goes like this,

1 2 3 4 5 1 2 3 2 1 2 5 1 3 2 1 4 4 3 2 5

I want to find the count of consecutive entries less than 5 and the count has to reset back to 0 if 5 is found and then start again from that 5 onwards

for example

1 2 3 4 --> gives 4
1 2 3 4 5 1 2 3 --> gives 3

1 2 3 4 5 1 2 3 2 1 2 5 1 3 2 1 4 4 3 2 5 --> gives 0

My basic idea is to find the most number of days a person has been scoring lesser than 5 and ofc it resets if he/she able to get 5 or above on a certain day. Therefore each value on a row is per day basis. (column means dates)

Help me out, Please. <3

EDIT: I also want blanks to be considered as 0.

real example: # 0.1 0.2 1 1.1 2 is the first row where '#' is blank, anything less than 1 should be counted and the first occurance of 1 or above value resets the count and counter starts again from that spot onwards, while ignoring all before it.

# # 1.1 2 --> gives 2
0.1 # 0.2 2 --> gives 3
## 0.1 1.1 0.1 --> gives 1
if no other choice then i can replace all blanks with '0' too

You say:

the count has to reset back to 0 if 5 is found

So why doesn't 1 2 3 4 5 --> 0 just like your third example that ends in 5?

If 1 2 3 4 5 --> 0 is incorrect then please explain further. Otherwise in the attached workbook cell A1 is the result and has the following formula:

Formula:

=COUNT($C$1:$W$1) - LOOKUP(2,1/($C$1:$W$1=5), COLUMN($C$1:$W$1)-2)

Your sequence is entered in cell C1 up to cell W1

Edit: this formula does not work if there are no 5's at all. See post #10 for a corrected formula.

Hope this helps, let us know.

My sheets is actually on onedrive. I tried ur formula and it doesnt work on web sheets. Is there any alternative?

Here is the code for UDF

Function ConNumbers(IP As Range)
Dim N As Long, X As Long, T As Long

N = IP.Cells.Count
If IP.Cells(1, N) = 5 Then
X = 0
Else
X = 1
For T = 2 To N
If IP.Cells(1, T) > IP.Cells(1, T - 1) Then
X = X + 1
Else
X = 1
End If
If IP.Cells(1, T) = 5 Then X = 0
Next T
End If
ConNumbers = X
End Function

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List

Hey,
Why is Y2 a 1 instead of a 3?

Thanks for the added rep!

Your edit earlier this afternoon to your post-1 requirements significantly changes the problem and puts all subsequent posts out of context which may confuse subsequent readers. It might have been better if your changed requirements were documented as a new post rather than editing post #1

My sheets is actually on onedrive. I tried ur formula and it doesnt work on web sheets. Is there any alternative?

I'm afraid I know nothing about Google sheets. The functions I use look like they are available in Google sheets. What exactly was the problem you saw? Your profile says MS Office version is 2019. Is that accurate?

My post #2 formula (for the original "reset at 5" problem) had a problem - it didn't deal correctly with number sequences with NO 5's present. Here is the amended formula:

Formula:

=IFERROR(COUNT($C1:$W1) - LOOKUP(2,1/($C1:$W1=5), COLUMN($C1:$W1)-2), COUNT($C1:$W1))

real example: # 0.1 0.2 1 1.1 2 is the first row where '#' is blank, anything less than 1 should be counted and the first occurrence of 1 or above value resets the count and counter starts again from that spot onwards, while ignoring all before it.
# # 1.1 2 --> gives 2
0.1 # 0.2 2 --> gives 3
## 0.1 1.1 0.1 --> gives 1
if no other choice then i can replace all blanks with '0' too

Like with the original problem, you say:

"the first occurrence of 1 or above value resets the count"

so why does # # 1.1 2 give 2 and not 0 ?? Same question for 0.1 # 0.2 2 why does this give 3 and not 0?? Please clarify.

Assuming that you can replace all blanks with '0' and if you agree with my expected results then the formula above with just one minor chaage in red should work.

Formula:

=IFERROR(COUNT($C8:$W8) - LOOKUP(2,1/($C8:$W8>=1), COLUMN($C8:$W8)-2), COUNT($C8:$W8))

Put the formula in C8 and your numbers in row-8 starting at col-C.

Attached is a revised version of kvsrinivasamurthy's workbook.

I have closed your follow-on thread, as it should not have been a separate thread.

Please continue here.

For the record, it sounds as if the OP has MS365 for Web Apps - the workbook is an Excel workbook (Microsoft credentials to log in) hosted on OneDrive.

There are instructions at the top of the page explaining how to attach your sample workbook.

=IFERROR(COUNT($C8:$W8) - LOOKUP(2,1/($C8:$W8>=1), COLUMN($C8:$W8)-2), COUNT($C8:$W8))

This formula worked perfectly when I tried it on MS excel on laptop but failed when it was used on MS365 web version.

Fails in what way? Error message? Wrong result? There is NOTHING inherent to that formula that should fail in the web app version of Excel, as far as I can see.

You really need to provide a workbook for testing and give more helpful detail about what's not working.

I saved to OneDrive, then went to the web app and opened it there:

OK - so, this begs a question: are you using Google Sheets after all? You REALLY need to get to grips with this.

Provide a screenshot like mine that includes the menu and title bar of the app. Let's get this resolved once and for all, and then everybody can stop running round in circles.

Right - so that's a completely different workbook.

Provide the workbook in the image for testing, please. It looks like you have failed to adapt the formula for the new workbook correctly.

At least we know that you are using MS365 - that's good.

I can't troubleshoot a picture. Please attach the workbook itself. Stop prevaricating, please, and do as you have been asked. You are wasting time by ignoring simple requests.

Try this:

=IFERROR(COUNT($D4:$AH4) - LOOKUP(2,1/($D4:$AH4>=1), COLUMN($D4:$AH4)-3), COUNT($D4:$AH4))

You have to update formulae to fit the dataset you are using.

AliGW on MS365 Insider (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	AC	AD	AE	AF	AG	AH	AI
1			Total	October
2
3	No	Name		1	2	3	4	5	6	26	27	28	29	30	31
4	1	000	0.3	0.1	0.1	0.1	0	0	0	0	0	0	0	0	0	31

Sheet: Sheet1

Your range starts in column D (the FOURTH column). Therefore, in the section that returns the column of the array, you need to take away 3 so that it starts counting at 1 instead of 4 (i.e. D=1, E=2, etc.).

Also why do I get negative results on some cells

No idea. I haven't bothered trying to get my head around the exact nature of what it is you are trying to do, just the mechanics of the formula.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

You're welcome.