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:

Category	Sub-category	Data
Breakfast	Light	Good
Breakfast	Light	Good
Breakfast	Medium	Bad
Breakfast	Heavy	Bad
Breakfast	Heavy	Good
Lunch	Light	Good
Lunch	Light	Bad
Lunch	Medium	Good
Lunch	Medium	Good
Dinner	Light	Bad
Dinner	Light	Bad
Dinner	Medium	Good
Dinner	Medium	Good
Dinner	Heavy	Good

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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"