Counting dates within a period of time

**Justinmih** · 11-05-2007, 09:45 PM

I am working on a formula to count how many times a date appears with a certain period of time, see what I have done so far
=SUMPRODUCT((F3:H3>Dates!A4)*(F3:H3<Dates!B4))

rather than have it check for dates between F3:H3 i need it to check F3, J3, N3, Q3 for the dates that fit the criteria however when i do it like this
=SUMPRODUCT((F3,J3>Dates!A4)*(F3,J3<Dates!B4))
it keeps coming up with #Value! when it should actually be 2

Any help on this would be great.

Thanks in advance

Justin.

**Justinmih** · 11-07-2007, 03:10 PM

I have re submitted this question.

**Ron Coderre** · 11-07-2007, 03:31 PM

Try this:

=INDEX(FREQUENCY((F3,J3,N3,Q3),Dates!A4:B4-{1,0}),2)

Here's how that works....
With 
Dates!A4: 01-JAN-2007
Dates!B4: 31-JAN-2007

The first part of the FREQUENCY function lists the cells to test.
(F3,J3,N3,Q3),

The second part creates 3 BINS....2 Explicitly....1 automatically.
Those bins are: 
1) Up through 31-DEC-2006
2) Over 31-DEC-2006 and up through 31-JAN-2007
3) (everything greater than 31-JAN-2007)

Each date in (F3,J3,N3,Q3) falls into one of those bins.
The only bin we care about is the 2nd bin
...Over 31-DEC and through 31-Jan

The INDEX function pulls the value from that bin.

Is that something you can work with?

**Justinmih** · 11-07-2007, 05:37 PM

Hi Ron,
Thanks so much for replying, it works brilliantly!

Thanks again

Justin.

**Justinmih** · 05-06-2008, 05:17 PM

Hi there,
Ron previously replied to this question.
I want to make a change to this formula but not sure how, =INDEX(FREQUENCY((F3,J3,N3,Q3),Dates!A4:B4-{1,0}),2)

Rather than the formula adding up the amount of times a date appears in the cells above, If a date appears in any the cells above I want it to read the name that is in B3 and display it in AB3
If anyone can help it would be much appreciated.

Thanks

Justin.

**daddylonglegs** · 05-06-2008, 06:44 PM

I assume you mean that if any one or more of F3, J3, N3 or Q3 are within the required date range you want B3 to appear? If so you should be able to enclose Ron's suggested formula in an IF function, i.e. in AB3

=IF(INDEX(FREQUENCY((F3,J3,N3,Q3),Dates!A4:B4-{1,0}),2),B3,"")