MODE for text with multiple criteria

g3lo18

New Member
Joined
Nov 2, 2013
Messages
35
Hi there,

I was hoping someone can assist me in crafting a formula that would look at text and return the most frequent appearing text provided it meets certain criteria.

Example data set:

[TABLE="width: 210"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Category[/TD]
[TD]Sub-category[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Light[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Light[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Medium[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Heavy[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Heavy[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Light[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Light[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Light[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Light[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Heavy[/TD]
[TD]Good[/TD]
[/TR]
</tbody>[/TABLE]


Objective:

Provided a specific category AND subcategory, what is the most common (MODE) text in column C ("Data").

For example, For Breakfast -> Light -> the most common data point is "Good".

Thank you!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Let's assume that A2:C15 contains the data, E2 contains the category of interest, and F2 contains the sub-category of interest. Try...

=INDEX($C$2:$C$15,MODE(IF($A$2:$A$15=E2,IF($B$2:$B$15=F2,MATCH($C$2:$C$15,$C$2:$C$15,0)))))

To allow Column C to contain empty cells for rows that meet the crtieria, try the following instead...

=INDEX($C$2:$C$15,MODE(IF($A$2:$A$15=E2,IF($B$2:$B$15=F2,IF($C$2:$C$15<>"",MATCH($C$2:$C$15,$C$2:$C$15,0))))))

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Upvote 0
Thank you for the prompt response! It works. However, if there is a tie, or only 1 category. For instance, in my example above Breakfast -> Medium = One data point or Breakfast -> Heavy = Tie the formula returns #N/A. Any way to fix that?

Perhaps if there is only 1 data, to output "1 Data Point", however, if its a tie "Tie between X and Y"
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

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
Back
Top