Hey all,

What I would love this spreadsheet to do is to count the number of "Hit" entries in column B that are on or between the start date and, depending on which one comes first, the End date or todays date. I would like this to be done in cell F5 under "Count". You can see the formula I have in there at the moment is not good enuff to do this but can only count ALL "Hit" entires regardless of date.

Put another way, I want to be able to change the start date and end date to whatever I like and see cell F5 re-calculate to show how many "Hit's" are on or between the dates indicated, and if a future end date is entered, to ignore this and instead use todays date.

Using Excel 2003, really hope someone can help me this is an extract of a much larger project and would add an incredible amount of quality if it can be done.

Sincerley,

Matty

SOLVED WITH:

=SUMPRODUCT((A2:A389>=C5)*(A2:A389<=E5)*(B2:B389=" Hit")*(A2:A389<=D5))

Thanks to anyone who has pondered over this, with a bit of intense gear grinding I worked this formula out that seems to do what I want (shown above).

If anyone has anything to add be happy to hear it.

Cheers guys

Hi Matt,

You can simplify your formula to one of this:
For consistency, I think the word Hit should also be put in a cell to be referenced.

A more efficient formula option would be to use the DCOUNT() function, but to use it you would have to structure your criteria fields differently. If you're interested I can give you an example of this.

Hope that helps...

Hey Colin,

Thanks for the reply, it was interesting to see how you used the MIN in there. I've also played around with DSUM and DCOUNT and they work great too.

Thanks heaps, this has solved a really big headache for me