count number of dates falling in the next seven days

  • Thread starter Thread starter Louisa
  • Start date Start date
L

Louisa

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?
 
B1=TODAY()
B2=TODAY()+7

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

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

adjust your ranges to suit
 
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
 
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?
 
Hello Louisa,

Then I suggest to use SUMPRODUCT:

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

resp.

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

Regards,
Bernd
 
Try the below formula. Change the M and F as required..

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


If this post helps click Yes
 
Back
Top