Formula to count if smallest number without a tie

[clortan]

I need a formula to get the result I manually typed in the last row.
Smallest with no ties. Not the 2nd or more smallest.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JamesCanale]

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Hole	1	2	3	4	5	6	7	8	9
2		Par	4	5	3	5	4	4	3	4	4
3	Fred	32	4	4	3	5	4	3	2	3	4
4	Harry	34	5	4	3	4	5	3	2	4	4
5	Tom	36	4	5	3	4	4	4	3	5	4
6	****	31	4	3	2	5	4	4	2	4	3
7	Mary	41	4	3	5	6	5	4	4	5	5
8
9
10
11		skins	0	0	1	0	0	0	0	1	1
Sheet20

Cell Formulas
Range	Formula
B3:B7	B3	=SUM(C3:K3)
C11:K11	C11	=--(COUNTIF(C3:C10,MIN(C3:C10))=1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:K10	Expression	=AND(COUNTIF(C$3:C$10,MIN(C$3:C$10))=1,C3=MIN(C$3:C$10))	text	NO

 

[clortan]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

365

 

[Fluff]

Thanks for that, please don't forget to update your profile.
Try the formula that James has suggested.

 

[clortan]

Wow, @JamesCanale ! that worked perfectly for the columns. I have tried at least 50X to get this! SO happy you helped me.

1 more favor=, please. At the end of the spreadsheet I need to calculate all the highlighted cells.

My brothers name is James too.

 

[clortan]

Thanks for that, please don't forget to update your profile.

Try the formula that James has suggested.

Thanks for that, please don't forget to update your profile.
Try the formula that James has suggested.

Thank you. I updated the profile. Have not been here since 2012, so appreciate the help.

 

[JamesCanale]

I'm not excited about row 12 here. I can't figure how to eliminate the need for that. I'd love to see that worked out.

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		Hole	1	2	3	4	5	6	7	8	9	Out	Hole	10	11	12	13	14	15	16	17	18	In	Total	Skins
2		Par	4	5	3	5	4	4	3	4	4	36	Par	5	3	4	4	3	5	4	4	4	36	72
3	Fred	32	4	4	3	5	4	3	2	3	4	32	38	7	2	5	4	2	6	3	4	5	38	70	4
4	Harry	34	5	4	3	4	5	3	2	4	4	34	38	7	3	5	4	5	4	4	3	3	38	72	1
5	Tom	36	4	5	3	4	4	4	3	5	4	36	42	6	4	5	5	5	7	4	4	2	42	78	1
6	Sam	31	4	3	2	5	4	4	2	4	3	31	38	5	3	4	4	3	4	5	5	5	38	69	2
7	Mary	41	4	3	5	6	5	4	4	5	5	41	38	5	4	4	5	3	4	5	5	3	38	79	0
8												0											0	0	0
9												0											0	0	0
10												0											0	0	0
11		skins	0	0	1	0	0	0	0	1	1		skins	0	1	0	0	1	0	1	1	1
12		best	4	3	2	4	4	3	2	3	3			5	2	4	4	2	4	3	3	2
Sheet20

Cell Formulas
Range	Formula
L2:L10,W2:W10	W2	=SUM(N2:V2)
X2:X10	X2	=W2+L2
Y3:Y10	Y3	=SUM((($C$11:$V$11=1))*($C3:$V3=$C$12:$V$12))
B3:B7,M3:M7	B3	=SUM(C3:K3)
C11:K11,N11:V11	C11	=--(COUNTIF(C3:C10,MIN(C3:C10))=1)
C12:K12,N12:V12	C12	=MIN(C3:C10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N3:V10	Expression	=AND(COUNTIF(N$3:N$10,MIN(N$3:N$10))=1,N3=MIN(N$3:N$10))	text	NO
C3:K10	Expression	=AND(COUNTIF(C$3:C$10,MIN(C$3:C$10))=1,C3=MIN(C$3:C$10))	text	NO

 

[Fluff]

Another option if you have the new HSTACK function.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1				Hole	1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18
2				Par	4	5	3	5	4	4	3	4	4
3	Fred			32	4	4	3	5	4	3	2	3	4	32	7	2	5	4	2	6	3	4	5			4
4	Harry			34	5	4	3	4	5	3	2	4	4	34	7	3	5	4	5	4	4	3	3			1
5	Tom			36	4	5	3	4	4	4	3	5	4	36	6	4	5	5	5	7	4	4	2			1
6	Sue			31	4	3	2	5	4	4	2	4	3	31	5	3	4	4	3	4	5	5	5			2
7	Mary			41	4	3	5	6	5	4	4	5	5	41	5	4	4	5	3	4	5	5	3			0
8
Report

Cell Formulas
Range	Formula
D3:D7	D3	=SUM(E3:M3)
N3:N7	N3	=SUM(E3:M3)
Z3:Z7	Z3	=SUM(--(BYCOL(HSTACK($E$3:$M$7,$O$3:$W$7),LAMBDA(bc,AND(INDEX(bc,ROWS(Z$3:Z3))=MIN(bc),SUM(--(MIN(bc)=bc))=1)))))

 

[Fluff]

Another option that doesn't need HSTACK

=LET(Data,$E$3:$W$7,r,ROWS(Data),SUM(--(MMULT(SEQUENCE(,r,,0),(E3:W3<Data)*(COLUMN(E3:W3)<>14))=r-1)))