How do you sum up an array which is a combination of numbers and errors? I faced this problem when carrying out a vlookup. The results_array is a combination of #N/A error and numbers as below:
On summing up Results_array through the formula
=sum(A2:A16) gives #N/A error.
To overcome this problem I made use of the following formula
A17 {=SUM(IF(ISNUMBER(A2:A16),(A2:A16),0))}
This is entered as an array formula with ctrl+shift+enter
The formula works in the same way as the results given in explanation array, where
B2=IF(ISNUMBER(A2),A2,0) and then summing up in B17
If you wish to count the occurences of numbers in the array A2:16 you could use this formula
A18 {=SUM(IF(ISNUMBER(A2:A16),1,0))}
Entered as an array formula with ctrl+shift+enter
Now some not so useful information 😉
724 incidentally is a sum of squares of 18 and 20. Try this =18^2+20^2
Or here is a function that you may use = SUMX2PY2(18,20)Have fun!