Find most frequent string

[sharky12345]

I'm trying to find the most frequent string in a range but with criteria.

I want to search the range E2:AH77 for the most common string that occurs but only in the rows where the value in column C matches "TEST". I have this, which works if I search a single column, (i.e. E2:E77), but gives me #N/A if I try to search the whole range, presumably because there are blank cells in that range too.

=INDEX(ISSUES!E2:E77,MODE(IF(ISSUES!C2:C77="TEST",IF(ISSUES!E2:E77<>"",MATCH(ISSUES!E2:E77,ISSUES!E2:E77,{0,0})))))

Can anyone help me move this forward please?

 

[Eric W]

Your formula isn't working because MATCH does not work on a 2-D range. That is in fact the toughest part of this question. Here's my best stab at it:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Most common value
2	x		TEST
3			TEST
4	Most common value		TEST		s
5	x		TEST			x		q
6			TEST			x					q
7			TEST		x		x		s
8	Value	Count	TEST		x								y	q
9	s	2	TEST
10	x	8			y		x
11	q	3	TEST						x
12	y	6	TEST		y					y	y	y
13			TEST		y
14			TEST		x
15			TEST		x
16			TEST
17
Sheet39

Cell Formulas
Range	Formula
A2	A2	=INDIRECT(TEXT(MOD(AGGREGATE(14,6,(COUNTIF(E2:AH77,E2:AH77)+ROW(E2:AH77)/100+COLUMN(E2:AH77)/10000)/(C2:C77="TEST")/(E2:AH77<>""),1),1)*10000,"R00C00"),0)
A5	A5	=INDEX(A9:A100,MATCH(MAX(B9:B100),B9:B100,0))
A9:A17	A9	=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($E$2:$AH$77)*100+COLUMN($E$2:$AH$77))/($C$2:$C$77="TEST")/($E$2:$AH$77<>"")/(COUNTIF(A$8:A8,$E$2:$AH$77)=0),1),"R00C00"),0),"")
B9:B17	B9	=IF(A9="","",SUMPRODUCT(($E$2:$AH$77=A9)*($C$2:$C$77="TEST")))

The A2 formula was my first effort. It looks good at first, but then I realized that it will include some rows that do not have TEST in them.

The next attempt starts will a formula to find all the unique, acceptable values. The formula in A9:A18 does that, drag it down as needed. Then the B9 formula counts the number of valid matches for each value. Finally, the A5 formula finds the maximum count, and picks it out of the A9:Axxx range.

Not especially neat, but you can put the helper formulas out of sight somewhere.

 

[sharky12345]

Thanks Eric - I'll have a play and see if I can use it.