Total to date formula
 

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

Total to date formula
 

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

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

--
Rick (MVP - Excel)


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

Total to date formula
 

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:D$7350=18),(B$2:B$7350))/20

Maybe all of this will help some. I hope.


"Paul B" wrote:

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

Total to date formula
 

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



"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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:D$7350=18),(B$2:B$7350))/20

Maybe all of this will help some. I hope.


"Paul B" wrote:

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