Aggregating costs by date

**perducci** · 10-06-2011, 12:02 PM

I have a table with several columns including date and amount ($). I'm trying to create a function that will take a month and the table as inputs, and return the SUM of the amounts listed in that month.

For example:

TABLE1

Date Amount
1/12/11 $10
1/30/11 $8
3/6/11 $15
8/20/11 $13
1/2/11 $4
6/5/11 $8

So I want to make a function =SUMMONTH(month,table or array)

In this case =SUMMONTH(January,TABLE1) = $22

**Kyle123** · 10-06-2011, 12:20 PM

No need to create a function, check out sumproduct/array formulas

You're looking for something like

=SUMPRODUCT(--(MONTH(A2:A5)=1),B2:B5)

Where 1 is the number of the month you want from 1 to 12

**Richard Buttrey** · 10-06-2011, 12:23 PM

Does the attached help

Regards

**perducci** · 10-06-2011, 01:05 PM

That did help. I ended up using this function:

=SUMIFS(Table2[Amount],Table2[Year],B1,Table2[Month],C1)

I had to create two extra columns that took the actual date and convert it to month and year for each row (Ex. 1/23/11 to 1 and 2011). Then by inputting "2011" in B1 and "4" in C1, the function yields the sum of the amounts in April 2011.

Another benefit to this is the ability to add rows to the table, and have the function update the sum for each month accordingly. This wasn't too hard of a problem, but thanks for your help!