Excel

manju shukla
manju shukla used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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
Microsoft Excel Expert
Top Expert 2014
Commented:
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
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
Ha!  Missed the MAX above - sorry
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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),))

Open in new window

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

Author

Commented:
Thanks, Thanks a lot. I have got my aswer.

Author

Commented:
Grateful for help.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Manju! Glad we could help.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
you are welcome manju

:)
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
closed as follows

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial