count instances if within 30 days

[wilg]

Hi guys, I have posted this workbook on another thread but am looking for something new.
Can you tell me how to count how many instances of "L" there are in a 30 day period from the attached workbook?
The instances could be from Jan 1st to Feb 28th. but I want a message to pop up if there is 3 instances within a 30 day period.

[macropod]

hi wilg,

In the worksheet, you can get the count via a formula like:
=SUMPRODUCT((B1:AF4>=MAX(B1:AF4)-30)*(OFFSET(B1:AF4,2,0)="LATE"))
(where B1:AF4 is populated progressively and the last date input is the date you're testing against), or:
=SUMPRODUCT((B1:AF4>=J4-30)*(OFFSET(B1:AF4,2,0)="LATE"))
(where J4 is the date you're testing against)

You could put such a formula into a hidden cell of worksheet, then reference it via your vba code. Alternatively, you could use:
Application.WorksheetFunction
to encode the formula directly into your vba code.

[wilg]

Hi Macropod, I've been trying to work with your suggestion, but It's not quite working out 100%.

I need to somehow reference the day in row 1 or 4 to know if there is 3 "Late" instances within a 30 day period to count the instances.

With you formula it counts instances from "AF" in february which only goes to "AC".

Let me know if you need more explanaiton.....sorry.

[mdmackillop]

There are different ways to interpret your question. What is a 30 day period? ... a month, every 4 week period which span months?
Pop up when? How is this to be triggered?
You might want to use dates rather than numbers for days and day names.

[macropod]

I've been trying to work with your suggestion, but It's not quite working out 100%.

I need to somehow reference the day in row 1 or 4 to know if there is 3 "Late" instances within a 30 day period to count the instances.

With you formula it counts instances from "AF" in february which only goes to "AC".

It's irrelevant to the formula that February only goes to AC. Since you're presumably including January in the test, the range needs to extend to AF. The controlling value is the one referenced by the 'J4' or 'MAX(B1:AF4)', depending on the approach you're taking.

Of course, it's possible you want something entirely differrent. I've been working on the assumption that you wanted to specify the date that the 30-day period terminates on. Perhaps you want to specify the starting date, or even just a count within any 30-day period, without regard to a start or end date. You really need to say exactly what you're after.

FWIW, you data are poorly laid out for this sort of thing: it really should all be laid out in two columns only - one for the date (though you could have a separate column for the day if that's really necessary) and one for the status. Then the formula would be much simpler. As it is, I suspect you're going to need to keep extending/adjusting the formula's row range as you add more months.