A formula that sums & indexes multiple arrays based on identifier?

Hi Everyone,

I am trying to do some YTD formulas for a monthly reporting package off our main forecast model involving actual, plan, and forecast.

One of the tasks is to sum expenses from our main P&L model. For example Salary is found within different groups (Marketing, G&A, etc.) on the P&L. In column C I have this marked. Within the same model the Actual, Plan, Fcst, and LY month arrays are laid out across by month. If it's August, I would like to sum up all the cells through August if column C is marked "Salary", so I can get to the correct total Salary figure.

How do I go about getting a YTD sum for each of these arrays while essentially doing a sumif on "Salary".

Thanks,

Rakib

So you can see below there are different areas in the P&L with salaries that I would like to sum together. Say the identifier is column B, while the 2012 months are found somewhere along the way in the sheet. What I want to do is get a total Salaries ytd through March (based on a numerical cell input that would indicate the month, e.g 3 for march), this formula would return 120 since it sums all the salaries through March.

Does that make sense?



Description Identifier Jan-12 Feb-12 Mar-12
Network operations - salaries Salaries 5 10 15
Customer Service - salaries Salaries 15 20 25
Sales/Account Managers - salaries Salaries 5 10 15

Rakib

Hi,

I think I simplified data too much, the salaries are found throughout column B, not sequentially, so the formula would need to involve some type of sumif locating all those marked salaries and then returning the YTD sum for salaries.

Rakib

Yes, column B will contain the word "Salaries" as a identifier corresponding to the different areas that have a salary line within a detailed P&L, so there are duplicate lines marked as "Salaries" within column B. My goal is to grab those lines and then sum the array based on what month it is.