The aim:
- Get the largest 5 numbers from (W115:AO115)
e.g. (5,5,5,5,4,3,3)==> Get (5,5,5,5,4) - 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?
>Solution :
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)))
