# Find/count consecutive entries in a row in excel

1. ## 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)

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

2. ## Re: Find/count consecutive entries in a row in excel

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:
`Please Login or Register  to view this content.`
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.

3. ## Re: Find/count consecutive entries in a row in excel

Here is the code for UDF
``Please Login or Register  to view this content.``
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

4. ## Re: Find/count consecutive entries in a row in excel

Thank you, I have edited my post to correct the mistake.

5. ## Re: Find/count consecutive entries in a row in excel

Thank you, I know nothing of UDF, So I will try this and will let you know.

7. ## Re: Find/count consecutive entries in a row in excel

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

8. ## Re: Find/count consecutive entries in a row in excel

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

9. ## Re: Find/count consecutive entries in a row in excel

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:
`Please Login or Register  to view this content.`

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:
`Please Login or Register  to view this content.`

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

Attached is a revised version of kvsrinivasamurthy's workbook.

10. ## Re: Find/count consecutive entries in a row in excel

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.

11. ## Re: Find/count consecutive entries in a row in excel

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.
update to our initial discussion, here anything above and equal to 1, the count has to reset, blank is considered 0 and 0.1 and 0.2 are to be counted.

12. ## Re: Find/count consecutive entries in a row in excel

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

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 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 coz the minimum score is 0.1 and maximum is 2.(possible values being 0.1, 0.2, 1, 1.1, 2)

Here anything above and equal to 1, the count has to reset, blank is considered 0 and 0.1 and 0.2 are the ones to be counted.

NOTE: I am currently using MS365 Web, so please let me know if it works there as well.

my first row is D4:AH4

GeoffW28's solution indicated that I must use '0' instead of blanks. His solution was

``Please Login or Register  to view this content.``
This formula worked perfectly when I tried it on MS excel on laptop but failed when it was used on MS365 web version.

Thank you so much if you made it this far and I really appreciate the time and effort anyone puts into this. Peace!!

13. ## Re: Find/count consecutive entries in a row in excel

Quick note:

To count: 0.1, 0.2, 0 and/or blanks
Trigger to reset count: anything above or equal to 1 (1, 1.1, 2)

14. ## Re: Find/count consecutive entries in a row in excel

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.

15. ## Re: Find/count consecutive entries in a row in excel

Attached a rough work file for clarification.

16. ## Re: Find/count consecutive entries in a row in excel

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

17. ## Re: Find/count consecutive entries in a row in excel

The cell where i input the formula shows red border and says "the formula in this cell contains an error"

18. ## Re: Find/count consecutive entries in a row in excel

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.

19. ## Re: Find/count consecutive entries in a row in excel

Here is the attachment.

20. ## Re: Find/count consecutive entries in a row in excel

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.

21. ## Re: Find/count consecutive entries in a row in excel

I cant attach links yet as it doesnt allow me to. Can i upload a txt file with the link in it?

22. ## Re: Find/count consecutive entries in a row in excel

updated formula for the targeted cells

23. ## Re: Find/count consecutive entries in a row in excel

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.

25. ## Re: Find/count consecutive entries in a row in excel

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
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

26. ## Re: Find/count consecutive entries in a row in excel

Thanks a lot, Can you please explain what that "-3" means? and how it was different from the earlier test sheets. Also why do I get negative results on some cells.

27. ## Re: Find/count consecutive entries in a row in excel

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.

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.

28. ## Re: Find/count consecutive entries in a row in excel

The negative numbers can be fixed if blanks are replaced with 0 and only if those blanks appear behind the last trigger value in any row. Blanks after it are ignored.

Capture.PNG

And that solves my problem 99% (the remaining 1% being able to keep blanks as it is), but its something I can manage. Thanks once again to everyone who spent time on this and helped me with this. Thanks to the moderator who helped remove all the clutters and kept this post clean.

Have a nice day.

29. ## Re: Find/count consecutive entries in a row in excel

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)