Advice on a formula

[datadummy]

I am looking for advice on a formula that will add up specific cells within a column, but will also recognize if a cell literally has NA in it and in a sense remove that from the formula so it wont error out when returning the sum of the other cells.

Example: formula is in cell P4 and that formula will look at cells P7, P10, P13 and P16 and will sum them up. That I can do, what I'm looking for is a formula that will still look in each cell to sum them but if one or more of those cells has NA that it wont error out my formula. Please let me know if you need more information. Thanks in advance.


<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></vath></v:stroke></v:shapetype><v:shape id="Picture_x0020_1" style="width: 98.25pt; height: 197.25pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:spid="_x0000_i1025"></v:shape>

 

[dreid1011]

I'll give those a try. So this is turning into a never ending question session lol, but is it possible to have a pivot table show your cell value of NA? It appears that regardless of formatting it always returns 0 instead of NA.

Well, if it's not already showing as NA, then I don't think a pivot table will fix that. If you're ok with it, I can pm you my email so you can send me a sample of the sheet.

 

[datadummy]

That would be great!