Nested if

[Closed]
Report
-
 Trowa -
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

1 reply

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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!