[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" wrote:

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

[Roger Govier[_3_]]

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

"DebbieV" wrote in message
...
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

[DebbieV]

On Jun 5, 6:49*pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
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

"DebbieV" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Thanks Roger - worked beautifully!