Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dsum date based on selection of months
Hello,
Can someone please lend a hand with the correct syntax to do the following? I have a column of dates within a named range database table. I am trying to setup the worksheet to allow my users to aggregate the daily data into specified months. So for a simple example I would be using "Table" as the named range for all columns, "Date" as the column with daily dates, and "Data" as the column name of the data I want to aggregate. I assume it would be best to have a table off to the side that would have the month numbers I need to aggregate and then have the Dsum formula reference that table? (criteria range) Date 1 2 5 To aggregate months 1, 2, and 5) Then Dsum(Table,"Data",criteria range) I issue is how do I get the formula to look for months versus actual dates? I suppose if I was doing a simple or < it would be simpler, but I need to allow ability to select non congruent months. Thanks in advance for any insights on how to get this done!! Greg |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dsum date based on selection of months
Sumproduct is less complicated:
A1:A20 = dates B1:B20 = values to sum E1:E3 = month numbers to sum = 1;2;5 =SUMPRODUCT(--(ISNUMBER(MATCH(MONTH(A1:A20),E1:E3,0))),B1:B20) Biff wrote in message ups.com... Hello, Can someone please lend a hand with the correct syntax to do the following? I have a column of dates within a named range database table. I am trying to setup the worksheet to allow my users to aggregate the daily data into specified months. So for a simple example I would be using "Table" as the named range for all columns, "Date" as the column with daily dates, and "Data" as the column name of the data I want to aggregate. I assume it would be best to have a table off to the side that would have the month numbers I need to aggregate and then have the Dsum formula reference that table? (criteria range) Date 1 2 5 To aggregate months 1, 2, and 5) Then Dsum(Table,"Data",criteria range) I issue is how do I get the formula to look for months versus actual dates? I suppose if I was doing a simple or < it would be simpler, but I need to allow ability to select non congruent months. Thanks in advance for any insights on how to get this done!! Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selection based on a date in a cell | Excel Discussion (Misc queries) | |||
Conditional formatting based on date range | Excel Discussion (Misc queries) | |||
Power users need your help €“ calculation based on a date | Excel Worksheet Functions | |||
Bucketing data based on DATE Range criteria | Excel Discussion (Misc queries) | |||
Calculate Start Dates based on Need-By Date? | Excel Worksheet Functions |