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
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
Last edited by Kyle123; 10-06-2011 at 12:29 PM. Reason: formula error
Click the * below to say thanks
Girls sleep with guys who use photoshop, but marry the ones who work with Excel
Corduroy pillows: They're making headlines!
Did you mean: recursion
http://www.google.com/search?hl=en&q=recursion
Does the attached help
Regards
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star iconat the bottom left of my post.
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks