Formula to ignore #N/A and sum values in a row - but HAS to return a #N/A if they are all #N/A

[Yukontornado]

I am trying to write a Formula to ignore #N/A and sum values in a row - but HAS to return a #N/A if they are all #N/A (messes up my graphs that are attached if a 0 is returned). Thank you!!

 

[Joe4]

Maybe something like this (for range N1:N30)

{=IF(COUNT(N1:N30)>0,SUM(IF(ISNA(N1:N30),0,N1:N30)),NA())}

Note that the squiggly brackets indicates that the formula has to be entered with CTRL+SHIFT+ENTER, not just ENTER.

 

[richh]

the AGGREGATE function can be used to bypass the errors. Try this

=IF(COUNTIF(A1:A10,NA())=COUNTA(A1:A10),NA(),AGGREGATE(9,3,A1:A10))

 

[Yukontornado]

Thank you Joe4. I don't have time to verify this today, but I will work with it on Monday!!

C.

 

[Yukontornado]

Maybe something like this (for range N1:N30)

{=IF(COUNT(N1:N30)>0,SUM(IF(ISNA(N1:N30),0,N1:N30)),NA())}

Note that the squiggly brackets indicates that the formula has to be entered with CTRL+SHIFT+ENTER, not just ENTER.

Hey Joe4, I just wanted to thank you that your solution worked perfectly!! Now I can stop pulling out what I have left of my hair, lol. I really appreciate your knowledge. Have a great day!

Chris

 

[Yukontornado]

the AGGREGATE function can be used to bypass the errors. Try this

=IF(COUNTIF(A1:A10,NA())=COUNTA(A1:A10),NA(),AGGREGATE(9,3,A1:A10))

Hi Richh,
I did not have a chance to test your solution, but the solution Joe4 provided worked nicely. If I get a chance to validate another solution I will try yours as well. Thank you for taking the time to post a solution to my problem! Have a great day.

Chris

 

[Joe4]

Hey Joe4, I just wanted to thank you that your solution worked perfectly!! Now I can stop pulling out what I have left of my hair, lol. I really appreciate your knowledge. Have a great day!

You are welcome.
Glad we were able to help.