Solved

# Excel

Posted on 2016-08-27
Medium Priority
51 Views
Is is possible to count    Max salary  of Male as well as    Max Salary of Female in an organization. For Exp -
Name Gender Salary
Rohan         M                   60000
Peeyush      M                  40000
Smita           F                   55000
Gauri           F                   35000
and so on. Using Function
0
Question by:manju shukla
• 3
• 2
• 2
• +1

LVL 99

Expert Comment

ID: 41772965
I use COUNTIF for this. Set M and F in a column and count the next column using the M/F column as the selector

https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
0

LVL 27

Accepted Solution

ProfessorJimJam earned 1000 total points (awarded by participants)
ID: 41772985
For male =Max(if(B:B="M",C:C))
For female =Max(if(B:B="F",C:C))

Formula should be entered using control shift enter

Also the example is given that gender is in column B and salary is in column C

You can change those range to actual range in your workbook
2

LVL 99

Expert Comment

ID: 41772994
Ha!  Missed the MAX above - sorry
0

LVL 33

Assisted Solution

Subodh Tiwari (Neeraj) earned 1000 total points (awarded by participants)
ID: 41773018
Assuming E3=M and E4=F and data is in the range A2:C5 then try this....
In F3
``````=MAX(INDEX((\$B\$2:\$B\$5=E3)*(\$C\$2:\$C\$5),))
``````
and copy down. No need to use Ctrl+Shift+Enter, Enter alone is sufficient as this is a regular formula.

Also you may use the Pivot Table to get the desired output.

Please refer to the attached for details.
Max-Salary.xlsx
1

Author Comment

ID: 41781111
Thanks, Thanks a lot. I have got my aswer.
0

Author Comment

ID: 41781112
Grateful for help.
0

LVL 33

Expert Comment

ID: 41781168
You're welcome Manju! Glad we could help.
0

LVL 27

Expert Comment

ID: 41781348
you are welcome manju

:)
0

LVL 27

Expert Comment

ID: 41808377
closed as follows
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overā¦
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month14 days, 9 hours left to enroll