Median with multiple conditions

[553]

Sheet 1 is my summary sheet which contains the following:
Cost Center Job Title Med
Accounting 1st VP Controller
Accounting Financial Analyst
Accounting General Accounting Mgr.
Accounting Jr. Accountant
Accounting Staff Accountant
Accounting SVP/CAO
Accounting Tres. Analyst
Accounting VP Budget
Accounting VP Treasury
Accounting Wire Desk Specialist
Accounting Wire Desk Supervisor
Exceutives 1st VP Controller
Exceutives Financial Analyst
Exceutives General Accounting Mgr.
Exceutives Jr. Accountant
Exceutives Staff Accountant
Exceutives SVP/CAO
Exceutives Tres. Analyst
Exceutives VP Budget
Exceutives VP Treasury
Exceutives Wire Desk Specialist
Exceutives Wire Desk Supervisor

I need the median of annual for each jobtitle within each cost center using the data from sheet 2:
Job Title Hourly Monthly Annual Cost Center
Financial Analyst Accounting
Financial Analyst Accounting
Financial Analyst Accounting
SVP/CAO Accounting
1st VP Controller Accounting
VP Treasury Accounting
VP Budget Accounting
Financial Analyst Accounting
General Accounting Mgr. Accounting
Staff Accountant Accounting
Wire Desk Supervisor Accounting
Wire Desk Specialist Accounting
Tres. Analyst Accounting
Jr. Accountant Accounting
Staff Accountant Accounting
Jr. Accountant Accounting
Financial Analyst Executive
Financial Analyst Executive
Financial Analyst Executive
SVP/CAO Executive
1st VP Controller Executive
VP Treasury Executive
VP Budget Executive
Financial Analyst Executive
General Accounting Mgr. Executive
Staff Accountant Executive
Wire Desk Supervisor Executive
Wire Desk Specialist Executive
Tres. Analyst Executive
Jr. Accountant Executive
Staff Accountant Executive
Jr. Accountant Executive

I am trying to get to this: If Cost Center = Accounting and the Job Title = Financial Analyst, Median Annual = xxxx

I would appreciate it very much if someone can help me out...I tred using a pivot table to make my live easier but it is not an option.

 

[firefytr]

Hi there,

Take a look at an Array Formula. It's syntax is something like this ...


Single criterion:
=AVERAGE(IF(A1:A10="condition",B1:B10))
Logic: For each row in A range, if cell(s) meet criterion, average those respective rows in B range.

Multiple criteria:
=AVERAGE(IF((A1:A10="condition")*(B1:B10=100),C1:C10))
Logic: Both conditions must be met, A range must equal text of "condition" and B range must equal numeric of 100, then average all respective C range with criteria met.

Note: If you want an OR condition (second example is an AND condition) change the * sign to a + sign.

HTH

-----------
Regards,
Zack Barresse

 

[553]

Hi Zack -
Thanks for the quick response. I tried the formula you provided but I am still getting a 0. Can I send you my spreadsheet?

 

[firefytr]

Hi there,

Sorry for the long delay, was abroad in the country for a week visiting family. You can email it to me if you'd like: firefytr AT vbaexpress DOT com.

-----------
Regards,
Zack Barresse