Countif top 5 highest value?

[eshnider21]

I have an issue how to use COUNTIF formula to count the top 5 highest value

EXAMPLE.xlsm
	A	B	C	D	E	F	G
1	COLUMN l	COLUMN 2	COLUMN 3	COLUMN4	COUNT OF TOP S VALUES
2	25	3	12	98		1	1
3	4	35	89	3		1	1
4	66	34	3	41		1	1
5	42	40	87	11		1	1
6	9	87	73	12		2	1
7	32	3	33	87		1	1
8	65	11	6	6		1	1
9	67	9	6	66		2	1
10	5	90	21	9		1	1
11	31	5	65	2		0	1
12	9	2	77	9		1	2
13	87	12	4	1		1	1
14	9	44	9	65		1	1
15	88	1	7	89		2	1
16	9	7	87	4		3	1
17	65	6	46	65		0	2
18	7	4	12	5		0	1
19	9	64	65	11		1	1
20	45	7	9	89		1	1
21	66	76	7	32		2	1
Table 1

Cell Formulas
Range	Formula
G2:G21	G2	=COUNTIF(A2:D2,LARGE(A2:D2,2))

 

[HongRu]

Try this.
Put the formula in E2, and copy E2 to E3:E21.

=(SUMPRODUCT((LARGE(A$2:A$21,ROW($E$1:$E$5))=A2)*1)>0)*1+
(SUMPRODUCT((LARGE(B$2:B$21,ROW($E$1:$E$5))=B2)*1)>0)*1+
(SUMPRODUCT((LARGE(C$2:C$21,ROW($E$1:$E$5))=C2)*1)>0)*1+
(SUMPRODUCT((LARGE(D$2:D$21,ROW($E$1:$E$5))=D2)*1)>0)*1

 

[eshnider21]

Solved