Help with formula - multiple conditions & ranking

[SlimJim77]

Hello everyone,

i'm pretty new to excel and i'd need some help with a formula.
This is how my sheet looks like

A	B	C	D
AX	W	bananas	apples
NE	W	limes
ZQ	L	computers	apples
ZQ	W	Books
BT	W	carrots	wine

Column A contains a list of 6 different Values to choose
Column B contains only 2 values
Column C+D containing a list with 30+ values, some cells are empty
The sheet has about 200 rows

What I want to evaluate is: What is the most frequent occurring value of column C+D ( also 2nd and 3rd most frequent) IF "ZQ" (column A) & "W" (column B) is given

If anyone knows how to do this i'd be more than happy!!

Thank you!
Jim

[martvg]

Hi SlimJim77,

Adding a column and creating a pivot table would be the easiest way to go I believe.

In Column E2, enter the following formula:
= C2&D2

For your data, this should return "bananasappels"
Now select all the data involved and create a pivottable.

Put column A and column B under "Report filter" and put column E in "Row Labels" and "Values".
This should result in a table showing exactly how often each combination occurred.
Via the filter menu, you can add the filters you listed.

Hope this helps!

Best regards

[SlimJim77]

Hi martvg,

thank you for your useful hint!
It was in the end even more easy just by applying the pivot table on the data from my sheet.
Now I can make all kinds of variations with just a few clicks, perfect!

Best,

SlimJim

[AliGW]

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

[SlimJim77]

Hello Ali,

i did mark it as solved and double checked if it's visible, and it is visible.

[AliGW]

Yes - you must have been doing so as I posted.