DSUM for date range
 

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

DSUM for date range
 

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

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



"Ken Ivins" wrote in message
...
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

DSUM for date range
 

Peo,

Thanks, that will do the trick. I appreciate your help.

Ken




"Peo Sjoblom" wrote in message
...
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

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



"Ken Ivins" wrote in message
...
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