Mode Function: Mutliple Cross referenced criteria

[AdamJones]

I'm making a spreadsheet for work purposes and cannot quite manage to figure out a formula for some of the data they require, the info is as follows:

TAB Name: APPS
Column A: Surname
Column B: Forename
Column C: Sex (M/F)

In a separate tab ('INFO') I need to calculate the most frequently occurring names based on sex. I have managed to work out how to get the mode of the names using =INDEX(APPS!B3:B100,MODE(MATCH(APPS!B3:B100,APPS!B3:B100,0))), but I cannot manage to figure out an equivalent formula which also accounts for the sex column.

Essentially what I have now is a mode of column A and a mode of column B, and what I need is a mode of column A for Males, a mode of column A for females, a mode of column B for males and a mode of column B for females.

I have tried =INDEX(APPS!B3:B100,MODE(IF(APPS!C3:C100,"M"(MATCH(APPS!B3:B100,APPS!B3:B100,0))))) but this doesn't work either (even when I try ctrl+shift+enter)

Help is much appreciated

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=INDEX(APPS!B3:B100,MODE(IF(APPS!C3:C100="M",MATCH(APPS!B3:B100,APPS!B3:B100,0))))

Hope this helps!

 

[AdamJones]

I tried the formula but when I CTRL+SHIFT+ENTER the cell shows #N/A with and the message shown when I hover over it says that 'A value is not available to the function or formula' - similar to previous attempts I had made.

Any knowledge as to what value./information the formula is missing?

Thanks for the help

 

[Domenic]

The formula will return #N/A when there are no duplicate data points in the data that meet the criteria. It will also return the error when one or more cells in Column B that correspond to the cells in Column C that meet the criteria are empty. To ignore empty cells, try...

=INDEX(APPS!B3:B100,MODE(IF(APPS!C3:C100="M",IF(LEN(APPS!B3:B100)>0,MATCH(APPS!B3:B100,APPS!B3:B100,0)))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[AdamJones]

I tried the new formula but again the #NA message was displayed ... I guess this may just be something that isn't quite manageable for me with my limited formula knowledge.

Thanks for the help!

 

[Domenic]

Did you make sure that there's at least two data points that meet the criteria?