Total to date formula


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

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


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



Using Excel 2003

Rick Rothstein

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



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:
Formula for total rainfall so far in 2009
Formula for average rainfall in February
Formula for average rainfall for February 18:

Maybe all of this will help some. I hope.

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.

Ask a Question