MODE for text with multiple criteria

[g3lo18]

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!

 

[Domenic]

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!

 

[g3lo18]

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"