Most common text with matched criteria

[DipDip]

Hi Everyone!
I hope you are keeping safe and well. I have a problem that I can't figure out and am looking for some help please.

I have created an excel tracker for recording our new employee of the month nominations and the top votes.

It looks like this...

Payroll No	Nominee Name	Nominated By	Clinical Days	Clinical Nights	Non-Clinical
123456	Joe Bloggs	Carl Cox	x
234567	Jim Bob	Ferry Corsten		x
345678	Karen Smith	Judge Jules			x

Then to the right I have another table which has the following:

Category	Nominee Name	Number of Votes
Clinical Days	{=IFERROR(INDEX(B3:B100,MODE(IF(D3:D100="X",MATCH(B3:B100,B3:B100,0)))),"No Overall Winner")}	=COUNTIF(B$3:B$100,Q3)
Clinical Nights	{=IFERROR(INDEX(B3:B100,MODE(IF(E3:E100="X",MATCH(B3:B100,B3:B100,0)))),"No Overall Winner")}	=COUNTIF(B$3:B$100,Q4)
Non Clinical	{=IFERROR(INDEX(B3:B100,MODE(IF(F3:F100="X",MATCH(B3:B100,B3:B100,0)))),"No Overall Winner")}	=COUNTIF(B$3:B$100,Q5)

The issue I am having is that if there is more than one person with the same number of votes, the person who appears on the list first is shown, as opposed to saying something like "No Overall Winner".

I'm hoping someone can help push me in the right direction.

Thanks for your help


Dip

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[DipDip]

Hi Fluff,
I'm using Office 365

 

[Fluff]

In that case how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Payroll No	Nominee Name	Nominated By	Clinical Days	Clinical Nights	Non-Clinical			Category	Nominee Name	Number of Votes		With LET
3	123456	Joe Bloggs	Carl Cox	x		x			Clinical Days	Joe Bloggs	2		Joe Bloggs
4	234567	Jim Bob	Ferry Corsten		X				Clinical Nights	Jim Bob	2		Jim Bob
5	345678	Karen Smith	Judge Jules			X			Non Clinical	No Overall Winner	0		No Overall Winner
6	123456	Joe Bloggs	Carl Cox	x		x
7	234567	Jim Bob	Ferry Corsten	x	X
8	345678	Karen Smith	Judge Jules			X
9
10
Data

Cell Formulas
Range	Formula
J3	J3	=IF(COUNT(MODE.MULT(IF(D3:D100="X",MATCH(B3:B100,B3:B100,0))))=1,INDEX(B3:B100,MODE.MULT(IF(D3:D100="X",MATCH(B3:B100,B3:B100,0)))),"No Overall Winner")
K3:K5	K3	=COUNTIF(B$3:B$100,J3)
J4	J4	=IF(COUNT(MODE.MULT(IF(E3:E100="X",MATCH(B3:B100,B3:B100,0))))=1,INDEX(B3:B100,MODE.MULT(IF(E3:E100="X",MATCH(B3:B100,B3:B100,0)))),"No Overall Winner")
J5	J5	=IF(COUNT(MODE.MULT(IF(F3:F100="X",MATCH(B3:B100,B3:B100,0))))=1,INDEX(B3:B100,MODE.MULT(IF(F3:F100="X",MATCH(B3:B100,B3:B100,0)))),"No Overall Winner")
M3	M3	=LET(Winner,MODE.MULT(IF(D3:D100="X",MATCH(B3:B100,B3:B100,0))),IF(COUNT(Winner)=1,INDEX(B3:B100,Winner),"No Overall Winner"))
M4	M4	=LET(Winner,MODE.MULT(IF(E3:E100="X",MATCH(B3:B100,B3:B100,0))),IF(COUNT(Winner)=1,INDEX(B3:B100,Winner),"No Overall Winner"))
M5	M5	=LET(Winner,MODE.MULT(IF(F3:F100="X",MATCH(B3:B100,B3:B100,0))),IF(COUNT(Winner)=1,INDEX(B3:B100,Winner),"No Overall Winner"))

The formula in col M is if you have got the new LET function.

 

[DipDip]

FLUFF you are awesome! Yes I do have the new LET function and that worked a treat. Cheers

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Fluff]

Just for the hell of it, you could also return the names of all those who were top like

Payroll No	Nominee Name	Nominated By	Clinical Days	Clinical Nights	Non-Clinical			Category	Nominee Name	Number of Votes
123456	Joe Bloggs	Carl Cox	x		x			Clinical Days	Joe Bloggs	2
234567	Jim Bob	Ferry Corsten		X				Clinical Nights	Jim Bob	2
345678	Karen Smith	Judge Jules			X			Non Clinical	Joe Bloggs, Karen Smith	0
123456	Joe Bloggs	Carl Cox	x		x
234567	Jim Bob	Ferry Corsten	x	x
345678	Karen Smith	Judge Jules			X

Cell Formulas
Range	Formula
J3	J3	=IFERROR(TEXTJOIN(", ",,INDEX(B3:B100,MODE.MULT(IF(D3:D100="X",MATCH(B3:B100,B3:B100,0))))),"No Overall Winner")
K3:K5	K3	=COUNTIF(B$3:B$100,J3)
J4	J4	=IFERROR(TEXTJOIN(", ",,INDEX(B3:B100,MODE.MULT(IF(E3:E100="X",MATCH(B3:B100,B3:B100,0))))),"No Overall Winner")
J5	J5	=IFERROR(TEXTJOIN(", ",,INDEX(B3:B100,MODE.MULT(IF(F3:F100="X",MATCH(B3:B100,B3:B100,0))))),"No Overall Winner")