No of entries between two dates

  • Thread starter Thread starter ArtySin
  • Start date Start date
A

ArtySin

Hi,
I have column Qwhich are dates and I need to count the number of date
entries for each week with the result in a separate column. However the date
entries change each time the sheet is updated and I thought that a cell
reference would be the answer but it doesn't work.
This works:
=SUM((COUNTIF($F$2:$F$46,">13/6/2009")-COUNTIF($F$2:$F$46,"<20/6/2009")))

But I really need to use a cell reference for the dates instead and this
doesn't work:
=SUMPRODUCT($F$2:$F$46>Q3)*($F$2:$F$46<Q4) where Q3= 13/06/2009 and Q4 =
20/06/2009

Anybody have any ideas please?
Thanks
 
You're missing a set of parens:

=SUMPRODUCT(($F$2:$F$46>Q3)*($F$2:$F$46<Q4))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi,
I have column Qwhich are dates and I need to count the number of date
entries for each week with the result in a separate column. However the
date
entries change each time the sheet is updated and I thought that a cell
reference would be the answer but it doesn't work.
This works:
=SUM((COUNTIF($F$2:$F$46,">13/6/2009")-COUNTIF($F$2:$F$46,"<20/6/2009")))

But I really need to use a cell reference for the dates instead and this
doesn't work:
=SUMPRODUCT($F$2:$F$46>Q3)*($F$2:$F$46<Q4) where Q3= 13/06/2009 and Q4 =
20/06/2009

Anybody have any ideas please?
Thanks
 
BTW - The proper formula syntax for the Countif() formula you tried to use
to reference cells containing your criteria would be:

=COUNTIF($F$2:$F$46,">"&Q3)-COUNTIF($F$2:$F$46,">="&Q4)

You'll also notice that your logic was wrong (">=") when you configured the
function in the first place.

Anyway, you're welcome and thanks for the feed-back.
 
Back
Top