SUM Cells with N/A

[DaFonz01]

Hello, I am trying to sum these cells with some of them having N/A. The cells that I am trying to add are:

D4,E6,F8,G10,H12,I14,J16,K18,L20,M22,N24,O26

Thanks for the help!

[ChemistB]

If some cells contain "N/A" as you say, then the SUM Function should work. If you're talking about some cells have the #N/A error then you have a problem. The easiest way to fix it is to fix your formulas so that the #N/A error doesn't show up (Use an IF statement to weed out the errors and replace them with blanks or zeros).

[DaFonz01]

I have a formula in the cells that have N/A that I do not want to change to "0". When I sum them up my total will say N/A. I have at least 1 valid number in the range of cells.

[rwgrietveld]

It is important that you specify if it is N/A as text (String) or #N/A which is the result of an error (probably a lookup or other reference function)

Like ChemistB States, the first can be solved, the second needs to be solved to change the reference function with an if. You could even include a "N/A" string if you would like that displayed.

Then a sumif(range,"<>"&"N/A",range) would work.

edit: Correction. The SUM would already be enough (just like ChemistB pointed out)

[NBVC]

Try:

=SUM(SUMIF(INDIRECT({"D4","E6","F8","G10","H12","I14","J16","K18","L20","M22","N24","O26"}),"<>#N/A"))

[DaFonz01]

Thanks NBVC! That worked like a charm.