-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Count consecutive cells that matching a criteria, Starting from the very first cell
- Thread starter Shameer
- Start date
-
- Tags
- consecutive count
Sham Let's get the ball rollin. We have to start somewhere. Now I modified your example, but the first 5 numbers should be the same. I have a question on your answer of 1. Your criteria of consecutive occurrences of values greater than 1% and less than 3% doesn't add up when 2.5, 1.31 and 2.18 would be 2 times. Unless I am misunderstanding your criteria, which is what I normally do, the example below answer should be 3. So let the question, comments or suggestions begin. We can always modify the solution to fit any criteria.
VBA Code:
Sub Prog1()
Dim LastCel As Integer
Dim count1 As Integer
count1 = 0
LastCel = Cells(Rows.Count, "C").End(xlUp).Row
For i = 5 To LastCel
Cells(i, 3).Select
If Cells(i, 3) > 0.01 And Cells(i, 3) < 0.03 And Cells(i + 1, 3) > 0.01 And Cells(i + 1, 3) < 0.03 Then
MsgBox Cells(i, 3)
count1 = count1 + 1
End If
Next i
Range("D3") = count1
End Sub| 23-10-01 work 1.xlsm | |||||
|---|---|---|---|---|---|
| B | C | D | |||
| 3 | Count is | 3 | |||
| 4 | |||||
| 5 | 246.38 | 1.56% | |||
| 6 | 240.5 | 4.04% | |||
| 7 | 244.12 | 2.50% | |||
| 8 | 246.99 | 1.31% | |||
| 9 | 244.88 | 2.18% | |||
| 10 | 255.7 | 5.00% | |||
| 11 | 262.59 | 4.00% | |||
| 12 | 266.5 | 1.25% | |||
| 13 | 265.28 | 2.30% | |||
| 14 | 274.39 | 8.00% | |||
Data | |||||
Upvote
0
Thanx for the lightning response #Exguy4u!!.
I guess I did not explain it well (which is I what I normally do
).
In the Column "Variations compared to Today", I need to count:
- cells that meet the criteria 'greater than 1% and less than 3%'
- starting from very First cell
- up to the cell after which the criteria fails
In the example I uploaded earlier, first cell of column meets the criteria. Second cell of column fails the criteria. So the count is 1.
In the new example I am uploading with this reply, the count would be be 3 (first 3 cells meets the criteria and 4th cell fails the criteria)).
Trust this explanation makes my question clearer.
Fyi, I see a very old post from 2017 that seems very similar to my question. My knowledge was not enough to adapt that formula for my need.
www.mrexcel.com
Thanx again.
I guess I did not explain it well (which is I what I normally do
).In the Column "Variations compared to Today", I need to count:
- cells that meet the criteria 'greater than 1% and less than 3%'
- starting from very First cell
- up to the cell after which the criteria fails
In the example I uploaded earlier, first cell of column meets the criteria. Second cell of column fails the criteria. So the count is 1.
In the new example I am uploading with this reply, the count would be be 3 (first 3 cells meets the criteria and 4th cell fails the criteria)).
Trust this explanation makes my question clearer.
Fyi, I see a very old post from 2017 that seems very similar to my question. My knowledge was not enough to adapt that formula for my need.
Count Consecutive Cells from a starting point
Hello. I am trying to have a formula that will count the number of consecutive cells using a starting point. I have used the threads to find the number of consecutive cells in a range but I only want to have the number of consecutive cells starting at the left most. Below is an example where...
Thanx again.
Attachments
Upvote
0
Sham, I believe I have covered all the different scenarios. Now the thing about this web site, in my opinion, is that how we ask the question to solve our problems is a big part. Once the community sees your input and output, using this XL2BB, they have a better idea of other solutions. So maybe an A+ student will weigh in and show us a better solution. In the meantime, this program should give you what you want.
VBA Code:
Sub Prog3()
Dim LastCel As Integer
Dim count1 As Integer
Dim Trap As Boolean
Trap = False
count1 = 0
LastCel = Cells(Rows.Count, "C").End(xlUp).Row
If Cells(5, 3) > 0.01 And Cells(5, 3) < 0.03 Then
Trap = True
End If
For i = 5 To LastCel
Cells(i, 3).Select
If Cells(i, 3) > 0.01 And Cells(i, 3) < 0.03 And Trap = True Then
Trap = True
count1 = count1 + 1
End If
If count1 >= 1 And Trap = False Then
Exit For
End If
If Cells(i + 1, 3) > 0.01 And Cells(i + 1, 3) < 0.03 Then
Trap = True
Else
Trap = False
End If
Next i
Range("D3") = count1
End Sub| 23-10-01 work 2.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | |||
| 3 | Count is | 1 | |||||||
| 4 | |||||||||
| 5 | 265.28 | -9.35% | -8.81% | -9.35% | 2.18% | -9.35% | |||
| 6 | 274.39 | 2.50% | -9.35% | -7.77% | -7.77% | 2.50% | |||
| 7 | 246.38 | -7.77% | -7.77% | 2.50% | 2.50% | -7.77% | |||
| 8 | 240.5 | -5.68% | 2.50% | -5.68% | -5.68% | -5.68% | |||
| 9 | 244.12 | 1.31% | 1.31% | 1.31% | 1.31% | 1.31% | |||
| 10 | 246.99 | 2.18% | 2.18% | 2.18% | -9.35% | 2.18% | |||
| 11 | 244.88 | -2.14% | -2.14% | -2.14% | -2.14% | -2.14% | |||
| 12 | 255.7 | -4.71% | -4.71% | -4.71% | -4.71% | -4.71% | |||
| 13 | 262.59 | -6.11% | -6.11% | -6.11% | -6.11% | -6.11% | |||
| 14 | 266.5 | -8.81% | -5.68% | -8.81% | -8.81% | -8.81% | |||
Data 3 | |||||||||
Upvote
0
bebo021999
Well-known Member
- Joined
- Jul 14, 2011
- Messages
- 2,467
- Office Version
- 2016
Upvote
1
Solution
Amazing....
Two simple questions.
I want to make the formula portable to start from any row. So, I changed the hard coded "-4" (see below image) to 'cell reference' (e.g., ROW(C4)).
Is that correct and will it work?? (It seems to work, but I prefer to have the Author's confirmation)
Second, in order to achieve the same results from a row of data (instead of column of data), will changing row to column, work (like in below example)??
Upvote
0
Thanx again #Exguy4u. I am an absolute rookie in VBA and could not implement your suggestion properly. I am sure someone else would find it useful in the near future.
Upvote
0
Thank you for your time and efforts
Upvote
0
Similar threads
