# Frequency Formula within a date range

Discussion in 'Business Applications' started by timmycl_7, Nov 19, 2008.

Not open for further replies.

Joined:
Nov 28, 2007
Messages:
50
Hi,

I have the formula below which counts the frequency at which the word "absent" occurs within the range B2:B366. If it occurs greater than or equal to 28 it displays a 1.

=SUM(IF(FREQUENCY(IF(Sheet1!B2:B366="Absent",ROW(Sheet1!B2:B366)),IF(Sheet1!B2:B366="PRESENT",ROW(Sheet1!B2:B366)))>=28,1))

Is there anyway to adapt the formula to count the frequency within a 12 week period (keeping the same cell range) whereby the first occurrence triggers the count of the 12 week period?

Any help would be appreciated!

Thanks

2. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Can you break that down in terms of why it's necessary to have anything more complicated than:

=IF(COUNTIF(B:B,"absent")>27,1,0)

?

3. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
As to this bit, I'm thrown by your reference to "first occurrence". Nevertheless, here's what I did.

1. Entered dates in A2:A366 ; 6th Nov 08 to 5th Nov 09 inclusive

2. Entered random values in B2:B366 ("absent" or "present")

3. Entered today's date (19th Nov 08) in D1.

Thus:

=SUMPRODUCT((A2:A366>=D1)*(A2:A366<=D1+83)*(B2:B366="Absent"))

gives you the number of "Absent"s from 19th Nov 08 for 12 weeks. HTH

As Seen On