Most frequent string in a column matched against a value in its ro

B

Babymech

I currently have two columns with several text entries - one column is the
'Category' column and the other is the 'Name' column. I've been able to find
out the most frequent string in Name by using the following

=INDEX(Name;MODE(IF(Name<>"";MATCH(Name;Name;0))))

However, what I want to know is the most frequent Name for a specific
Category - for example, the most frequent Name string whenever the Category
string is "Buyer". I tried doing this by using SUMPRODUCT and multiplying the
Match(Name) value with (Category="Buyer") but that only returned #N/A (even
as an array,formula) so either I got the syntax wrong or I was thinking along
the wrong lines.

Any good ideas? Thanks.
 
B

Bernie Deitrick

Array enter:

=INDEX(Name,MODE(IF(Name<>"",IF(Category="Buyer",MATCH(Name,Name,0)))))

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formulas in arrays and named ranges 2
IF(AND) in an array formula 3
best method to determine "most frequent value" from a dynamic list 1
Formula to Count and Return Most common Value in a Dynamic Named Range 1
Most frequent item in a list (excluding zeros) 3
Returning matched employee number from a different sheet 2
Formula to Count and Return Most common Value in a Dynamic Named Range 1
Identifying more than one item in a list 8

Top