Complex If/Then Formula Help Needed

[CMP1538]

For some reason my company's firewall won't let me us the VBA screenshot method, and I'm not allowed to post attachments, so I'm going to try and explain the best I can.

I'm working on a formula that will help automate grouping a budget, I did a search and didn't see anything like this, so hopefully it's not a repeat.

I have an excel sheet with 30 columns of data, down the left are various G/L Account codes that correspond with a G/L descriptor (i.e. Airfare, meals, payroll), the columns identify each department.

*At the bottom (row 184) I have each department identified as "D" for direct labor related, "O" for overhead, and "G" for general/admin.
*Column B has numbers from 1-50 identifying each row that corresponds with various line item groupings (11 is Salaries and Wages, 14 is benefits, 25 is communications, etc)

I need to come up with a formula that will compare the groupings to this table, and sum any figure that matches that, by the identified subgrouping.

For Example:
Column "AP" will correspond with Column "B" (as in cell AP52=14, I need to find all instances where 14 shows up in Column B)
Column "AQ" corresponds with Row 184 (as in Cell AQ52=G, I need to only search for the instance above in columns where "G" shows up in Row 184)

Here are the dimensions of my spreadsheet.

Column B4:B180 are labels
Column D4:AL180 contains data to be summed
Column AP contains data matching somewhere in column B
Column AQ contains data matching somewhere in row 184

I hope that is enough to go on without being able to post a screenshot to the post.

Please respond with any questions, or other data you may need.

 

[alansidman]

Without seeing an actual sample worksheet, the best I can offer is that you may be well served to create a Pivot Table. This will allow you to filter/sort the data.