# Total to date formula

P

#### Paul B

I have rain fall data from 1-1-1989 to present

I am trying to get the amount of rain fall we should have to date, like you
see on the TV weather.

I have the dates in A2 down with a defined name as Dates, with this formula
=OFFSET(Data!\$A\$2,0,0,COUNTA(Data!\$A:\$A),1)

I have the amounts in B2 down with a defined name as Rainfall, with this
formula

=OFFSET(Data!\$A\$2,0,1,COUNTA(Data!\$A:\$A),1)

Data is only put in when there was rain fall, so all the dates for the years
are not there, would be like this, in A2 1-3-1989, B2 .09, in A3 1-8-1989,
B3 1.04, there are some T's in column B when there was only a trace for
that day

I have been tried some formulas but just can't get it, this is what I have
so far, may not even be close, I don't think the <=day part is right but..
The /20 is for the number of years, if this is right can it also be included
in the formula

Array formula

=SUM(IF(ISNUMBER(Dates)*ISNUMBER(Rainfall)*(MONTH(Dates)<=MONTH(TODAY()))*(DAY(Dates)<=DAY(TODAY())),Rainfall))/20

Thanks

Using Excel 2003

R

#### Rick Rothstein

I think this formula will do what you want (total rainfall for the current
month)...

=SUMPRODUCT((A2:A10000>=DATE(YEAR(TODAY()),MONTH(TODAY()),1))*B1:B10000)

J

#### JLatham

You say rainfall to date - do you mean for the month, or year?

Anyhow, it would help you if you added 3 helper columns on each row (C, D
and E). Have each one pull out part of the date that is in column A; with C
holding the month, D holding the day and E holding the year.

Then you can use a SUMPRODUCT() formula to get totals.

Assuming your data goes from row 2 down through row 7350.

Formula for total rainfall for 2008:
=SUMPRODUCT(--(E\$2:E\$7350=2008),(B\$2:B\$7350))
Formula for total rainfall so far in 2009
=SUMPRODUCT(--(E\$2:E\$7350=2009),(B\$2:B\$7350))
Formula for average rainfall in February
=SUMPRODUCT(--(C\$2:C\$7350=2),(B\$2:B\$7350))/20
Formula for average rainfall for February 18:
=SUMPRODUCT(--(C\$2:C\$7350=2),--(D\$2\$7350=18),(B\$2:B\$7350))/20

Maybe all of this will help some. I hope.

P

#### Paul B

Thanks for taking a look at this, I have formulas for the amount of rain by
month and year. I found out that For all the major cities, the National
Climatic Data Center has calculated what they call daily normal rainfalls
using 30 year averages so I am using this data instead. Thanks

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.