Unfortunately I can't get your formula to work. can be some Formating
problems (we use ; instead of , for example and it might be something else
that I have missed correcting).

Ano

"vezerid" wrote:

On May 29, 2:35 pm, ano wrote:
Hi,
I want a cell to display the average from a column B. Column A shows dates,
starting 20070419 and ending 20070629, and I want to display the average from
20070419 till today. and only based on filled cells.

I can solve it by using some extra columns but I would like it as easy as it
could be done.

Hopefully
ano

(Im using a Swedish version explaining why I might use the wrong terms)

If the dates are stored as text:

=SUMPRODUCT(($A$1:$A$100="20070419")*($A$1:$A
$100<=TEXT(TODAY(),"yyyymmdd")*$B$1:$B$100)/SUMPRODUCT(($A$1:$A
$100="20070419")*($A$1:$A$100<=TEXT(TODAY(),"yyyy mmdd")*($B$1:$B
$100<""))

If the dates are stored as numbers and formatted as yyyymmdd,

=SUMPRODUCT(($A$1:$A$100=DATE(2007,4,19))*($A$1:$ A$100<=TODAY())*$B
$1:$B$100)/SUMPRODUCT(($A$1:$A$100="20070419")*($A$1:$A
$100<=TEXT(TODAY(),"yyyymmdd")*($B$1:$B$100<""))

HTH
Kostis Vezerides