Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel |