Mode Function: Mutliple Cross referenced criteria

AdamJones

New Member
Joined
Apr 1, 2016
Messages
9
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

New Member
Joined
Apr 1, 2016
Messages
9
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

New Member
Joined
Apr 1, 2016
Messages
9
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Did you make sure that there's at least two data points that meet the criteria?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,061
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top