# Top 3 Value only if repeating twice or more

#### yogpat

##### New Member
Hi Friends,

How to retrieve Top 3 Max value if it’s that value repeating 2 times or more in excel column.
Function must return max value only & not blank.

Thanks
Eg.

 Value 0 50 2 8 2 5 4 7 7 5 7 150 Ans Top 1 - 7 Top 2 - 5 Top 3 - 3

#### GraH - Guido

##### Well-Known Member
Try,
[F2] =LARGE((COUNTIFS(\$A\$2:\$A\$14,\$A\$2:\$A\$14)>1)*(\$A\$2:\$A\$14<(IF(F1>0,F1,99^99)))*(\$A\$2:\$A\$14),E2)

E2:E4 = 1,2,3

#### Attachments

• 9.3 KB Views: 5

#### Peter Bartholomew

##### Well-Known Member
Excel 365 is a very different program, so
Code:
``````= LET(
multiplicity, COUNTIFS(numbers, numbers),
distinct, UNIQUE(FILTER(numbers, multiplicity>1)),
INDEX(SORT(distinct,,-1), k) )``````

#### Attachments

• 10.2 KB Views: 1

#### yogpat

##### New Member
Excel 365 is a very different program, so
Code:
``````= LET(
multiplicity, COUNTIFS(numbers, numbers),
distinct, UNIQUE(FILTER(numbers, multiplicity>1)),
INDEX(SORT(distinct,,-1), k) )``````
Any way in older version.

#### yogpat

##### New Member
Try,
[F2] =LARGE((COUNTIFS(\$A\$2:\$A\$14,\$A\$2:\$A\$14)>1)*(\$A\$2:\$A\$14<(IF(F1>0,F1,99^99)))*(\$A\$2:\$A\$14),E2)

E2:E4 = 1,2,3

I am using Microsoft Excel 2007. whenever i change value in column my excel get hang & re-open.

#### yogpat

##### New Member

I am using Microsoft Excel 2007. whenever i change value in column my excel get hang & re-open.

I have change 99^99 to 1^3. Its working.

Thanks a lot.
Yog

#### GraH - Guido

##### Well-Known Member

I am using Microsoft Excel 2007. whenever i change value in column my excel get hang & re-open.

Thank for letting us know your version. Would not know a reason why it hangs.
You can replace 99^99, it's just a big number, by max of range +1.

Also strange it's working with 1^3 as that results in 1. Or I don't understand my own formula.

#### Excel Wizard

##### Active Member
Try at C2:C4

=LARGE(IF(FREQUENCY(\$A\$2:\$A\$14,\$A\$2:\$A\$14)>1,\$A\$2:\$A\$14),ROWS(C\$2:C2))

Ctrl+Shift+Enter

#### yogpat

##### New Member
Thanks, I have changed to 10^3