SumIf with #NA

[Chris3177]

Hello All,

I would like to sum two cells not next to each other, but in some cases one of the cells is equal to #N/A. How do I sum the following cells:

L8 and H8 if H8 is #N/A?
10 and #N/A the result should be 10, but I can't seem to figure out which formula to us.

Thank you!

Chris3177

 

[Andrew Poulsom]

Welcome to MrExcel.

Like this?

=SUM(IF(ISNUMBER(H8),H8),L8)

But can't you avoid having #N/A in that cell in the first place?

 

[barry houdini]

Hello Chris welcome to MrExcel, try this formula

=IF(ISNA(H8),0,H8)+IF(ISNA(L8),0,L8)

 

[Chris3177]

I am not sure, but the formula works perfectly! Thank you very much!

 

[Chris3177]

Hello Chris welcome to MrExcel, try this formula

=IF(ISNA(H8),0,H8)+IF(ISNA(L8),0,L8)

This formula works as well. Thank you again!

 

[Chris3177]

I am trying to find the QUARTILEs of a column, but within the column there are #N/A. How does one write a formula to sum all the "numbers" ignoring the non-numbers (#N/A) and return the specified quartile?

Thank you!

 

[Aladin Akyurek]

I am trying to find the QUARTILEs of a column, but within the column there are #N/A. How does one write a formula to sum all the "numbers" ignoring the non-numbers (#N/A) and return the specified quartile?

Thank you!

Example...

Control+shift+enter, not just enter:

=QUARTILE(IF(ISNUMBER(A2:A11),A2:A11),1)

 

[Chris3177]

Example...

Control+shift+enter, not just enter:

=QUARTILE(IF(ISNUMBER(A2:A11),A2:A11),1)

Works perfectly!

Thank you!

Chris

 

[Aladin Akyurek]

Works perfectly!

Thank you!

Chris

You are welcome. Thanks for providing feedback.