Previous Topic Next Topic

Nested if

Closed
tosh - Dec 9, 2009 at 05:36 PM
 Trowa - Dec 10, 2009 at 08:36 AM
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
Related:

1 response

Answer 1 / 1
Trowa
Dec 10, 2009 at 08:36 AM
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
0

Similar discussions

Nested IF functions using time values
SharkMerry -
Harshada Chaudhari -

5 responses