prioritizing budgets with functions and then summing

I have a matrix of equipment I wish to buy over three plan ~ 15 entries. I have itemized them by a 1, 2 or 3 priority over the next 3 years with the cost of the equiment in a separate cell. I would like to create a summary matrix showing the cost of priority 1, 2 and 3 for each year (3 by 3 matrix). I want to be able to adjust the priorities and have the summary matrix calculate new sum. I have tried what ifs and it was painful.....actually everything I tried was painful. I am hoping someone can help me to understand which function to use to create a simple total cost matrix that I can manipulate depending upon which priority I put in cell.

thanks in advance for any insight you can offer.

Would something like this work? There is an input table for your 15 inputs (Equipment, price, priority) and a summary matrix that takes equipment price and divides by total years (in this case 3).

SUMIFS(cost_array, priority_array, priority, year_array, year)

Make the array references objective not subjective (syntax of $A$1:$A$10 instead of A1:A10 for example) and then you can pull across to fill up your matrix just by changing the "priority" & "year" references.

ben_hensel,

Thanks for the feedback, this is really helping me get a better grasp of excel. Do you mean something like this?

simply amazing - that looks so easy. I would have never attempted it that way but then again I couldnt get it to work. I struggled with getting it to sum in the one cell.

Thanks you so much. Jennifer

This was my line of thinking - see attached concept. I should have attached this in first email - it may have explained better. So columns B,C&D rows 28-30 would reflect my budget cost for each year by priority and I could alter the totals by moving around the priorities of each equipment request. Again, thank you for showing me the way.

I adjusted your original worksheet with SUMIFS functions to do the totaling, but the way it is setup, you can't have different costs for each line of equipment in later fiscal years, but use whatever works for you.

Thank you jjhayes. I am trying to understanding the formula from the first worksheet. All of the formulas are the same in the summary matrix - how does the function read which priority or year so it knows what to sum? Sorry not too schooled in land of excel functions and programming.

Hopefully the screenshot can help explain what the formula is doing. I am not an Excel expert by any stretch, but you can learn a whole lot by playing around in Excel and following this forum as well.

ahh the dummies version lol -pictures and text is just what I needed. Thank you again JJ hayes and ben hensel.

jjhayes - could you breakdown the second version as you did with the first? I thought I understood the "right" but I do not nor do I understand the "1" after the $A34. much appreciated.

I think you are referring to the formula

=SUMIFS(Costs,FY_15,RIGHT($A28,1))

I tried to leave the CAPEX shell file in the format you had submitted. I named some ranges (the Fiscal Years and the Costs). I then just used the Right($a28,1) in the sumifs formula to bucket the costs in the right priority. It is basically pulling the right most character (either a "1", "2", or "3") to make sure it is putting cost in right priority as well as fiscal year.

Ahh I get it now - totally logical - i just needed the definitions I guess. Thank you so much!