Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

ERROR #NUM! from sum of largest 5 numbers

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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?

Excel table

>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)))
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading