YTD Sum of Multiple Years
Hello out there,
New to this site so excuse any lack of knowledge of accepted posting standards. I'm having trouble doing a year to date sum. Referencing the attached workbook, I want to be able to write a formula that can look at a month of the year, use it as a base, and sum up everything above that cell within a range. I would like to do this without using individual month dates if at all possible. Thank you.
First time poster with a good thread title, well articulated issue, and an attached example. You're off to a good start.
I5: =SUM(INDIRECT("F4:F" & 3 + MATCH($H5,$C$4:$C$15,0)))
J5: =SUM(INDIRECT("E4:E" & 3 + MATCH($H5,$C$4:$C$15,0)))
K5: =SUM(INDIRECT("D4:D" & 3 + MATCH($H5,$C$4:$C$15,0)))
and copy downwards
INDIRECT is used to target a string, which is then evaluated as a range, which means =INDIRECT("A" & 100 - 99) will still target A1.
Using this we can build dynamic expressions. The first formula is summing F4:F ??? where ??? equals 3 + where it finds a match to the date. The first formula evaluates to F4:F7 and the August's becomes F4:F11. The rest follow this pattern.
Last edited by daffodil11; 03-13-2014 at 06:52 PM.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Thank you, I'm not sure how to hit "solved" for this but i did figure out how to give you reputation. Thanks again!
Glad I could help out.
At the top under Thread Options, click that dropdown menu and choose Mark as Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks