Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Solved

Posted on 2012-09-20

With reference to this question, further criteria are set in counting consecutive months:

1. as soon as consecutive 6 months is reached, the consecutive months count will restart again. For example, Jan-Jun2012= 6 consecutive months. July to Sept 2012 will be another 3 consecutive months. and so on. there is no running /overlapping calculation.

Thus max consecutive 3 months = 4 times and max consecutive 6 times = 2 times in a year.

2. Consecutive months to be counted within each year only (for example, if it is Oct-Dec 2011, and then Jan -Mar 2012, it can only be counted consecutive 3 times for 2011 and 2012 respectively and NOT 6 times.

1. as soon as consecutive 6 months is reached, the consecutive months count will restart again. For example, Jan-Jun2012= 6 consecutive months. July to Sept 2012 will be another 3 consecutive months. and so on. there is no running /overlapping calculation.

Thus max consecutive 3 months = 4 times and max consecutive 6 times = 2 times in a year.

2. Consecutive months to be counted within each year only (for example, if it is Oct-Dec 2011, and then Jan -Mar 2012, it can only be counted consecutive 3 times for 2011 and 2012 respectively and NOT 6 times.

2 Comments

```
Function ConsecutiveCount(Letter As String, Occurence As Integer, TheRange As Range) As Integer
Dim i As Long
For Each c In TheRange
If c.Value Like "*" & Letter & "*" Then
i = i + 1
If i >= Occurence Then
ConsecutiveCount = ConsecutiveCount + 1
i = 0
End If
Else
i = 0
End If
Next
End Function
```

as for the second part of the question:

the data is not in bands of more than one year, so it should not matter.

If your data is set out diferently to allow for more years in the columns, post an example and will see how to make the formula work.

Consecutives-count.xls

Thus max consecutive 3 months = 4 times and max consecutive 6 times = 2 times in a year.

sorry, i think i typed wrongly max consecutive 3 months =

Jan-Mar, May-July, Sept-Nov=3 times

For example, a row 52 there is a hit from Apr - Dec- this should be

Apr-Jun - consecutive 3

Jul- Sept - consecutive 3 but since from Apr-Sept, therefore upgrade to consecutive 6

Oct-Dec- since hit a max of consecutive 6, thus reset back to consecutive 3

Consecutive 3= 2 times

Consecutive 6= 1 time

2nd part of question:

yes i agree with you, since it is on a per year basis, so no overlapping.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.

Course of the Month15 days, 6 hours left to enroll

Join the community of 500,000 technology professionals and ask your questions.