Most frequent value given condition

[enphynity]

I am trying to get the most common color sold of a particular frame given a large list of frames. I came across another thread with a similar question but the check data was numerical so the formula given was:

=INDEX(E1:E20000,MODE(IF(A1:A20000=C2537,IF(E1:E20000<>"",MATCH(E1:E20000,E1:E20000,0)))))

Obviously this won't work for text since the MODE function is only for numbers. I need a similar formula that will work for textual values.

Many thanks

[Kaper]

A quick answer proposition without insight in (sample data) attachment:

May be you shall use pivot table with your color as row headers counter in value field and condition(s) as filter(s).
Then ask Pivot table to show only max value?

[FDibbins]

Perhaps take a look at using countifS() function?

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[enphynity]

So my columns look like this:

Frame Color
------- --------
Adam II Gunmetal
Adam II Gunmetal
Adam II Brown
Adam II Gold Antique
David Burgundy
David Gold
David Brown
David Gold
Katelyn Rose
Katelyn Rose
Katelyn Brown
Katelyn Walnut Fade
Nathan Gold
Nathan Brown
Nathan Mauve
Nathan Mauve

I would like to know the most common color sold for each frame. In the above data I would get gunmetal for the "Adam II" frame, Gold for the "David" frame, rose for the "Katelyn" frame and mauve for the "Nathan" frame

[Kaper]

reading from forum rules:

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

I still suggest Pivot table

[enphynity]

Workbook attached...pivot table is not what I am looking for

[daddylonglegs]

You can use the INDEX/MODE formula - the data doesn't have to be numbers for that to work - MATCH returns a number and then MODE returns a row number based on that (with INDEX returning the relevant item) so that should work OK for you here, e.g.

=INDEX(B$2:B$700,MODE(IF(A$2:A$700="Arnie",IF(A$2:A$700<>"",MATCH(B$2:B$700,B$2:B$700,0)*{1,1}))))

confirmed with CTRL+SHIFT+ENTER

[benishiryo]

@dll:
would this do the same without the array constant?
=INDEX(B$2:B$70,MODE(IF(A$2:A$700="Arnie",IF(B$2:B$700<>"",MATCH(B$2:B$700,B$2:B$700,0)))))

since A2:A700 is already checked for "Arnie".

[daddylonglegs]

Yes, apologies, the "blank check" should be against column B, as you say, perhaps not even required if all non-blank rows are populated in both columns A and B.

The {1,1} part just ensures that you get back a result even when there is a single entry (as MODE normally returns #N/A when no number is repeated)

[enphynity]

Thanks so much dll, works great!!

[arlu1201]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.