Help with counting between dates

[DebbieV]

Hi

I have a spreadsheet where I want to multiply the number of dates that
appear in a column between a range by a value.


I have used the following formula to count the number of date entries
that have a value of greater than 0 (or 0/01/1900 as it is formatted)
and then multiplied it by 600 to get a $ value for the number of dates
in that column.
=COUNTIF($W$3:$W$1000,">0")*600

But because the formula brings dates that could be in the future, I
only want to include dates up to the current date ie today's date.

Any ideas?

cheers
Debbie

 

[BobT]

You can use an Array formula (a.k.a. CSE formula). If you have your dates in
a column (e.g. E4 to E23) and you want to count the number of dates that are
prior to or equal to Today() and multiply that count by 600 (or some cell's
value), enter the following formula in a cell:

=SUM(IF(E4:E23<=TODAY(),1,0))*600

But instead of pressing ENTER, presse CTRL + SHIFT + ENTER. This creates an
arrary formula:

{=SUM(IF(E4:E23<=TODAY(),1,0))*600}

This basically does the test (the IF part) for each cell in the range E4:E23
then sums the results and multiplies that sum by 600. All you need to do is
change the range (E4:E23) to meet your needs and the test (<=Today()).

Bob Tulk
MOUS (XP/97)

 

[DebbieV]

You can use an Array formula (a.k.a. CSE formula).  If you have your dates in
a column (e.g. E4 to E23) and you want to count the number of dates that are
prior to or equal to Today() and multiply that count by 600 (or some cell's
value), enter the following formula in a cell:

=SUM(IF(E4:E23<=TODAY(),1,0))*600

But instead of pressing ENTER, presse CTRL + SHIFT + ENTER.  This creates an
arrary formula:

{=SUM(IF(E4:E23<=TODAY(),1,0))*600}

This basically does the test (the IF part) for each cell in the range E4:E23
then sums the results and multiplies that sum by 600.  All you need to do is
change the range (E4:E23) to meet your needs and the test (<=Today()).

Bob Tulk
MOUS (XP/97)










- Show quoted text -

Thanks for the info Bob but will this return all entries between a
range of dates or just those prior to the current date. eg I need
them to occur between 0/01/1900 and today.

many thanks
Debbie

 

[Roger Govier]

Hi Debbie

Just modify your formula to deduct dates greater tan today
=(COUNTIF($W$3:$W$1000,">0")-COUNTIF($W$3:$W$1000,">"&TODAY()))*600

 

[DebbieV]

Hi Debbie

Just modify your formula to deduct dates greater tan today
=(COUNTIF($W$3:$W$1000,">0")-COUNTIF($W$3:$W$1000,">"&TODAY()))*600

--
Regards
Roger Govier











- Show quoted text -

Thanks Roger - worked beautifully!