Excel Formula; MODE texts based on two criteria

[Legacy 509827]

Guys,

Please help! I am trying to find the mode of column D (Most frequent product) with criteria of "Discrepancy" & "Snack Cake"

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G
1
2
3	Type of Issue	Date	Production Lines	Product			Mode
4	Discrepancy		Snack Cake	Twinkie			HoHo
5	Discrepancy		Snack Cake	HoHo
6	Discrepancy		b	Twinkie
7	Discrepancy		b	HoHo
8	a		Snack Cake	Muffin
9	Discrepancy		Snack Cake	HoHo
10	b		b	Muffin
11	Discrepancy		Snack Cake	Muffin
12	c		b	Twinkie
13	Discrepancy		Snack Cake	Twinkie
14	Discrepancy		b	HoHo
15	e		Snack Cake	HoHo
16	e		Snack Cake	Twinkie
17	Discrepancy		Snack Cake	HoHo
18
Sheet2

Cell Formulas
Range	Formula
G4	G4	=INDEX(D4:D17,MODE(FILTER(MATCH(D4:D17,D4:D17,0),(A4:A17="Discrepancy")*(C4:C17="Snack Cake"))))

 

[Legacy 509827]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G
1
2
3	Type of Issue	Date	Production Lines	Product			Mode
4	Discrepancy		Snack Cake	Twinkie			HoHo
5	Discrepancy		Snack Cake	HoHo
6	Discrepancy		b	Twinkie
7	Discrepancy		b	HoHo
8	a		Snack Cake	Muffin
9	Discrepancy		Snack Cake	HoHo
10	b		b	Muffin
11	Discrepancy		Snack Cake	Muffin
12	c		b	Twinkie
13	Discrepancy		Snack Cake	Twinkie
14	Discrepancy		b	HoHo
15	e		Snack Cake	HoHo
16	e		Snack Cake	Twinkie
17	Discrepancy		Snack Cake	HoHo
18
Sheet2

Cell Formulas
Range	Formula
G4	G4	=INDEX(D4:D17,MODE(FILTER(MATCH(D4:D17,D4:D17,0),(A4:A17="Discrepancy")*(C4:C17="Snack Cake"))))

Thank you! However, this unfortunately did not work on my spreadsheet. It returned a #CALC error, tried changing it around and kept returning N/A error.

 

[Eric W]

Sorry, I expected more rows. The formula will return the N/A error if there's only 1 value. Try this variation instead:

Book1
	A	B	C	D	E	F	G
1
2
3	Type of Issue	Date	Production Lines	Product			Mode
4	Discrepancy		Snack Cake	Twinkie			Twinkie
5
Sheet2

Cell Formulas
Range	Formula
G4	G4	=INDEX(D4:D17,MODE({1,1}*FILTER(MATCH(D4:D17,D4:D17,0),(A4:A17="Discrepancy")*(C4:C17="Snack Cake"))))

The only difference is the {1,1}* part. You'll still get the CALC error if there are no matching rows.

 

[Legacy 509827]

Hey thank you so much! Both actually worked, the first one I just tested one row. I realized I needed to put more than the one than one row. The sheet will be populated daily so it will be filled so its fine. Tweaked it a bit to add if empty/if error criteria for the #calc error, and its great. Definitely could not figure this one out! Thanks again.

I had a small concern on the FILTER function incase someone with a 2019 excel file views the file if they will see the result… but I’d test it out.

 

[Eric W]

This version should work on older versions of Excel:

=INDEX(D4:D17,MODE(IF((A4:A17="Discrepancy")*(C4:C17="Snack Cake"),MATCH(D4:D17,D4:D17,0)*{1,1},"")))

It needs to be confirmed with Control+Shift+Enter, not just Enter. Glad I could help!