Using DSUM based on Dates

[matthoffman33]

Hi,

I have three columns. They are Station, Date, and Rainfall. This
database will go from May 1 'till today's date with several stations
included. I want to add up all of the rainfall amounts from one
station from May 1 'till 14 days ago, 13 days ago, 12 days ago, and so
on up to today. I've been trying to use the DSUM function but I need
to add in the second set of criteria so it will only add the amounts up
untill however many days ago. Any suggestions??

Thanks,

Matt

 

[Peo Sjoblom]

I don't understand what you mean by " From May 1 until today's date" then
you say "14 days ago"
If the date starts with May 1st and today is May 5th, that is only 5 days or
do you mean in the future when today is May 14th? Anyway you can most
definitely use more than one criteria with the D functions but you might
need a formula for the criteria

=DSUM(MyTable,"Rainfall",F1:F2)


assume the 3 column table is called "MyTable", replace with real name or
cell range, the criteria range is
F1:F2, leave F1 empty and in F2 put

=AND(A6=$C$2,B6<=TODAY())

where A6 is the first cell in the Station column with data (not header) and
C2 is the cell where you put
the name of the station, note the absolute reference, that is necessary. B6
is the first cell with the data in the dates column. This formula will sum
all the station in C2 where the date is less than or equal to today,
if you want 14 days from today and until today change the formula to

=AND(A6=$C$2,B6<=TODAY(),B6>=TODAY()-14)

then you can just change the -14 to 13,12 and so on

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"matthoffman33" <[email protected]>
wrote in message
news:[email protected]...