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

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