DSUM for date range

K

Ken Ivins

Can I use the DSUM feature to look at a data sheet and return the sum of all
values which occurred in a given month? Example:

Fields - Date Premium etc.


I want to look at the date field and find for each month what the total
premium are.

I started with DSUM('2004'!$A$6:$AI$471, Premium, ???????)

Not sure how to word the rest. An "If" statement, maybe?

Any ideas would be helpful.

Thanks,
Ken Ivins
 
P

Peo Sjoblom

You can use this

=SUMPRODUCT(--(YEAR('2004'!A7:A471)=2004),--(MONTH('2004'!A7:A471)=10),'2004
'!B7:B471)

where I assumed that the dates started in A7 and the premum in B7, replace
accordingly. If you want to use DSUM you need to create a criteria range,
assume that range is in the same sheet that holds the the formula while the
data sheet is named 2004, now to create the criteria you can use

Date Date
=10/01/04 <=10/31/04
Click to expand...

so you need to use 4 cells, assume E1:F2, then the formula would look like

=DSUM('2004'!A6:AI471,"Premium",E1:F2)

both these will work for October of 2004 and only with real numerical dates


--

Regards,

Peo Sjoblom
 
Top