nested if Closed

Question

Hello,
I'm tring to figure out an if statement and I'm not able to find it online anywhere.

I have a spreadsheet that consist of employees at several locations (CA, TX, NY, IL). The spreadsheet also includes their salaries at each location. I have another sheet that has their supervisor salaries at the location. How can I count the number of employees at that location that are making more than the specified supervisor salary?

I've tried to use Countif, Counta, Sumproduct, but I can't get it to work. I am able gather the number of employees making more than one supervisor salary, but its including all locations. I'm really seeking for each specific location (comparison of employee vs supervisor).

Thanks

Trowa · Answer

Hi Tosh,

If I understand you correctly your sheets look a bit like this:

SHEET1
Employee	Location	Salary
A	CA	10
B	TX	12
C	NY	11
D	IL	13
E	TX	20
F	TX	25

SHEET2
Supervisor	Location	Salary
G	CA	15
H	TX	15
I	NY	10
J	IL	12

Now you would like to know how many employees at a certain location have a higher salary then their supervisor at the same location.
Example: Location TX has 3 employees (B, E, F) of which 2 of them (E, F) have a higher salary then their supervisor (H).

Start by sorting SHEET1 by location:
SHEET1
Employee	Location	Salary
A	CA	10
D	IL	13
C	NY	11
B	TX	12
E	TX	20
F	TX	25

Now use the following formula in a cell on sheet1 (or change the sheet refference in formula):
=SUM(IF(C5:C7>Sheet2!C3,1,0))
Confirm formula by hitting Ctrl+Shift+Enter and the result will be 2.

Best regards,
Trowa

Nested if

1 response

Similar discussions

Subscribe To Our Newsletter!