I have a worksheet with a list of arrival dates. I need to count how many of
these dates fall within the next seven days. This calculation would need to
be self-updating on a daily basis. Is this possible?

Assuming you have your dates in ColA. Try the below formula. If tried today
this will give you the count of dates from 04/30/09 to 05/06/09 both
inclusive.. You can adjust to suit your requirement


=COUNTIF(A:A,"<" & TODAY()+8)-COUNTIF(A:A,"<" & TODAY()+1)

If this post helps click Yes
---------------
Jacob Skaria


"Louisa" wrote:

I have a worksheet with a list of arrival dates. I need to count how many of
these dates fall within the next seven days. This calculation would need to
be self-updating on a daily basis. Is this possible?

That's brilliant, thank you so much. I've just realised that i have an added
complication in that i need separate arrival date tallys for men and women. I
have entered gender in column B as M or F, is it possible to add this as a
condition in the formula below?

"Jacob Skaria" wrote:

Assuming you have your dates in ColA. Try the below formula. If tried today
this will give you the count of dates from 04/30/09 to 05/06/09 both
inclusive.. You can adjust to suit your requirement


=COUNTIF(A:A,"<" & TODAY()+8)-COUNTIF(A:A,"<" & TODAY()+1)

If this post helps click Yes
---------------
Jacob Skaria


"Louisa" wrote:

I have a worksheet with a list of arrival dates. I need to count how many of
these dates fall within the next seven days. This calculation would need to
be self-updating on a daily basis. Is this possible?

Try the below formula. Change the M and F as required..

=SUMPRODUCT(--(A$1:A$100TODAY()),--(A$1:A$100<TODAY()+8),--(B$1:B$100="M"))


If this post helps click Yes
---------------
Jacob Skaria


"Louisa" wrote:

That's brilliant, thank you so much. I've just realised that i have an added
complication in that i need separate arrival date tallys for men and women. I
have entered gender in column B as M or F, is it possible to add this as a
condition in the formula below?

"Jacob Skaria" wrote:

Assuming you have your dates in ColA. Try the below formula. If tried today
this will give you the count of dates from 04/30/09 to 05/06/09 both
inclusive.. You can adjust to suit your requirement


=COUNTIF(A:A,"<" & TODAY()+8)-COUNTIF(A:A,"<" & TODAY()+1)

If this post helps click Yes
---------------
Jacob Skaria


"Louisa" wrote:

I have a worksheet with a list of arrival dates. I need to count how many of
these dates fall within the next seven days. This calculation would need to
be self-updating on a daily basis. Is this possible?

Hello Louisa,

Then I suggest to use SUMPRODUCT:

=SUMPRODUCT(--(A1:A999<TODAY()+8),--(A1:A999TODAY()),--(B1:B999="M"))

resp.

=SUMPRODUCT(--(A1:A999<TODAY()+8),--(A1:A999TODAY()),--(B1:B999="F"))

Regards,
Bernd

B1=TODAY()
B2=TODAY()+7

=SUMPRODUCT(($A$1:$A$100B1)*($A$1:$A$100=<B2))

=COUNTIF($A$1:$A$100,"=<"&B2)-COUNTIF($A$1:$A$100,""&B1)

adjust your ranges to suit


On 29 Kwi, 11:58, Louisa wrote:
I have a worksheet with a list of arrival dates. I need to count how many of
these dates fall within the next seven days. This calculation would need to
be self-updating on a daily basis. Is this possible?