I have months across the top and other criteria (unit and region) down the left side in columns A and B. I am trying to come up with a formula that will dynamically sum the appropriate columns (months) based on an input cell that will be the last day of the applicable month. The problem is that my data set will grow and shrink in length (rows) but will stay static in column width. I also need the ability to evaluate multiple criteria in columns A and B. Sample file is attached. Thanks!!
How about with a helper column...
In K2 and copied down >> =A2&B2
Then in N6 >> =SUMIF($K$2:$K$9,"AX",INDEX($C$2:$J$9,,MATCH($N$5,$C$1:$J$1,0)))
HTH
Regards, Jeff
Thanks for the reply. I think there are two issues that I see. First, it looks like your formula sums the values for that particular month whereas I am looking to sum all of the months up to and including the month selected. i.e your formula resulted in 34, I am looking for 153 for unit A, region X thru Feb. Second, I have an issue where my data will grow in length so unfortunately I need to dynamically reference the range.
N6 =sumproduct(($a$2:$a$9="a")*($b$2:$b$9="x")*index($c$2:$j$9,0,match($n$5,$c$1:$j$1,0)))
humpaco,
Welcome to the forum!
Attached is a modified version of your provided workbook.
It contains two different solutions. The first uses a sumproduct formula. The second is a slightly shorter array formula.
I also rearranged the output table slightly.
In Cell N1 you enter the end date
In column M starting in M3 and down you enter the Unit
In column N starting in N3 and down you enter the Region
In cell O3 (YTD total using sumproduct) and copied down is this regular formula:
Please Login or Register to view this content.
In cell P3 (YTD total using array formula) and copied down is this array* formula:
Please Login or Register to view this content.
*Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how it gets surrounded by the curly braces {}, do not try to add those manually.
You can use whichever solution you feel more comfortable with. Is that what you were looking for?
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
tigeravatar,
Thanks for the welcome to the forum. Great resource for a wealth of information.
The formula's work perfectly. However, my data set will grow and shrink in size (rows only...columns will stay static). Is there a way to make the sumproduct formula dynamic to accommodate this? I can see where it would be inefficient but I like to use the sumifs formula because you can select entire columns. I can't apply that here because the range of numbers I am trying sum is a different size (# of columns) than the columns of criteria. I guess I could use the indirect function to make the sumproduct formula dynamic. Either that or I could create named ranges using a combination of counta on the rows and the offset function. Any suggestions?
Charlie
@tigeravatar, I hope you don't mind, but I took a stab at making your formulas dynamic. Please let me know if you would have approached it differently or if I went astray.
@Charlie, see if this new attachment will help.
Charlie,
Your second instinct is right on the money:
Originally Posted by humpaco
I would absolutely use dynamic named ranges (which is pretty much what you described) in order to make that happen. Here are some links for information regarding dynamic named ranges:
http://support.microsoft.com/kb/830287
http://www.contextures.com/xlNames01.html
@Jeff, looks good to me
Jeff,
Works like a champ. Thanks!!
I actually did solve the puzzle but my solution was just a little different from yours. I (dynamically) named the data range "rngData" and set it equal to "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),11)". From there I built my formula "=SUMPRODUCT((OFFSET(rngData,0,2,1,9)<=$N$1)*(OFFSET(rngData,1,0,ROWS(rngData)-1,1)=M3)*(OFFSET(rngData,1,1,ROWS(rngData)-1,1)=N3)*OFFSET(rngData,1,2,ROWS(rngData)-1,9))".
I think the major difference between ours is I only needed 1 named range. The trade off is that my formula is much longer. You used multiple ranges but your formula is therefore more concise....I like yours better.
Thanks so much to you and tigeravatar.
We are glad to hear you have a working solution and thanks for the feedback.
===========================================================
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
hi can any one help me to sum YTD totals based on account number and month of reporting.
excel.JPG
in the sheet..i want to get YTD total as of June for account number 300000.. similarly there are many account numbers where i want to drag the formula to arrive at YTD numbers,
@urchandru
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks