SUMIF excluding #N/A
 

I am trying to add different cells located in different worksheets (tabs)
within the same workbook. I know I can use =SUMIF(A1:A5,€�<#N/A€�) without
error.

The problem I am having is trying to add different cells not grouped
together while using SUMIF. Example, =SUMIF(A1+A3+B5+C20, €�<#N/A€�). If B5
=#N/A for instance, then my SUMIF will not work.

Any solutions?

Thank you in advance for help.

SUMIF excluding #N/A
 

I know it looks odd, but this appears to work:

=SUMIF(A1,"<#N/A") + SUMIF(A3,"<#N/A") + SUMIF(B5,"<#N/A") +
SUMIF(C20,"<#N/A")

"Scott A" wrote:

I am trying to add different cells located in different worksheets (tabs)
within the same workbook. I know I can use =SUMIF(A1:A5,€�<#N/A€�) without
error.

The problem I am having is trying to add different cells not grouped
together while using SUMIF. Example, =SUMIF(A1+A3+B5+C20, €�<#N/A€�). If B5
=#N/A for instance, then my SUMIF will not work.

Any solutions?

Thank you in advance for help.

SUMIF excluding #N/A
 

If the only cells with #N/A are one of the cells you are referencing then
you could do something like this

=SUMIF(A2:B9,"<#N/A")-SUM(A3,A5,A6,A8,B2:B9)

Note these cell addresses are just a sample.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Scott A" wrote:

I am trying to add different cells located in different worksheets (tabs)
within the same workbook. I know I can use =SUMIF(A1:A5,€�<#N/A€�) without
error.

The problem I am having is trying to add different cells not grouped
together while using SUMIF. Example, =SUMIF(A1+A3+B5+C20, €�<#N/A€�). If B5
=#N/A for instance, then my SUMIF will not work.

Any solutions?

Thank you in advance for help.

SUMIF excluding #N/A
 

Thank you for the suggestion. I understand your recommnedation, but the
problem I run into is number of characters in the formula.

I provided an abreviated version. In reality, I need to sum approx 20 cells
over about 9 worksheets. Not all of the cells are in the same cell reference
location. For the time, I am using a hidden sheet to capture the data
grouped together and using a SUMIF from that. I am just trying to find a
cleaner way.

I appreciate any suggestions.



"JLatham" wrote:

I know it looks odd, but this appears to work:

=SUMIF(A1,"<#N/A") + SUMIF(A3,"<#N/A") + SUMIF(B5,"<#N/A") +
SUMIF(C20,"<#N/A")

"Scott A" wrote:

I am trying to add different cells located in different worksheets (tabs)
within the same workbook. I know I can use =SUMIF(A1:A5,€�<#N/A€�) without
error.

The problem I am having is trying to add different cells not grouped
together while using SUMIF. Example, =SUMIF(A1+A3+B5+C20, €�<#N/A€�). If B5
=#N/A for instance, then my SUMIF will not work.

Any solutions?

Thank you in advance for help.