Complex If/Then Formula Help Needed

CMP1538

New Member
Joined
Jul 22, 2009
Messages
8
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,361
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,644
Messages
5,676,967
Members
419,665
Latest member
allsmilees2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top