The aim:

1. Get the largest 5 numbers from (W115:AO115) e.g. (5,5,5,5,4,3,3)==> Get (5,5,5,5,4)
2. Add them together ,i.e . 5 5 5 5 4=24

My formula is :=LARGE(W115:AO115,1) LARGE(W115:AO115,2) LARGE(W115:AO115,3) LARGE(W115:AO115,4) LARGE(W115:AO115,5)

#NUM! ERROR happens ,I guess the reason is there is no 5 numbers in this row

In fact U should be count as 0 . The result of this row (2,4,U,3,U,3) should be 12. How to solve this case?

CodePudding user response：

To avoid errors like #NUM! or #VALUE! need to wrap within IFERROR()

Use either,

=SUM(IFERROR(LARGE(IFERROR(W115:AO115,""),ROW($1:$5)),""))

Or, as mentioned above by Harun Sir, using AGGREGATE() still needs to wrap within IFERROR()

=SUM(IFERROR(AGGREGATE(14,6,W115:AO115,ROW($1:$5)),""))

Edit,

One more way, without using IFERROR() instead using ISNUMBER()

=SUM(AGGREGATE(14,6,ISNUMBER(W115:AO115)*W115:AO115,ROW($1:$5)))

CodePudding user response：

You could also use Sort in Excel 365, assuming they are positive numbers:

=SUM(INDEX(SORT(N( W115:AO115),,-1,1),SEQUENCE(5)))