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]

You could either make a named range of those 4 cells, then use a SUMIF like so:

=SUMIF(SumRange,"<>NA")

Or something longer..

=SUM(IF(P7<>"NA",P7,0),IF(P10<>"NA",P10,0),IF(P13<>"NA",P13,0),IF(P16<>"NA",P16,0))

 

[MUKESHY12390]

Here you go..
solution : =SUM(IF(ISERROR(A:A),"",A:A)) '''' Just change range according to your need

this is an Array formula , so don't forget to Press CTR+ENTER

 

[datadummy]

You could either make a named range of those 4 cells, then use a SUMIF like so:

=SUMIF(SumRange,"<>NA")

Or something longer..

=SUM(IF(P7<>"NA",P7,0),IF(P10<>"NA",P10,0),IF(P13<>"NA",P13,0),IF(P16<>"NA",P16,0))

I tried the longer formula and it appears to be working but I now have discovered a new issue with a formula that feeds the first one I posted about. Here is my new formula that I need to tweak,

=IF(COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"NA",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"),"NA",COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"Y",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"))

My issue with this formula is that for column C "Post Surg" there is currently no data entered so my formula is returning a 0, but I would like it to return NA to reflect no data entered. Hope this makes sense.

 

[dreid1011]

I tried the longer formula and it appears to be working but I now have discovered a new issue with a formula that feeds the first one I posted about. Here is my new formula that I need to tweak,

=IF(COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"NA",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"),"NA",COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"Y",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"))

My issue with this formula is that for column C "Post Surg" there is currently no data entered so my formula is returning a 0, but I would like it to return NA to reflect no data entered. Hope this makes sense.

It doesn't look like you are actually testing the COUNTIFS vs anything..

=IF(COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"NA",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017")  [B][COLOR=#ff0000]logical test goes here[/COLOR][/B]   ,"NA",COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"Y",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"))

What should the first COUNTIFS return to make the result "NA"?

 

[datadummy]

It doesn't look like you are actually testing the COUNTIFS vs anything..

=IF(COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"NA",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017")  [B][COLOR=#ff0000]logical test goes here[/COLOR][/B]   ,"NA",COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"Y",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"))

What should the first COUNTIFS return to make the result "NA"?

The first COUNTIFS for this particular example should return NA because there currently is no "Post Surg" data for the month of May. I'm not sure if I need to separate my formula more or revamp it completely. Did I answer the question?

 

[dreid1011]

The first COUNTIFS for this particular example should return NA because there currently is no "Post Surg" data for the month of May. I'm not sure if I need to separate my formula more or revamp it completely. Did I answer the question?

Er, sort of. So, if there is no "Post Surg", the COUNTIFS should return 0?

 

[datadummy]

Er, sort of. So, if there is no "Post Surg", the COUNTIFS should return 0?

After rethinking it more I think I have overcomplicated it and came up with a solution using the existing formulas and data. Thanks for the assistance though!!

 

[dreid1011]

After rethinking it more I think I have overcomplicated it and came up with a solution using the existing formulas and data. Thanks for the assistance though!!

I was thinking that you could just turn your formula around too with what you have and it should do what you want:
=IF(COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"NA",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"),COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"Y",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"),"NA")

or

=IF(COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"NA",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017")=0,"NA",COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"Y",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"))

Making the highlighted adjustments (if you haven't already done that) should work as well.

 

[datadummy]

I was thinking that you could just turn your formula around too with what you have and it should do what you want:
=IF(COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"NA",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"),COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"Y",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"),"NA")

or

=IF(COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"NA",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017")=0,"NA",COUNTIFS('Enteric Bundle'!$C:$C,"Post Surg",'Enteric Bundle'!E:E,"Y",'Enteric Bundle'!$A:$A,">4/30/2017",'Enteric Bundle'!$A:$A,"<6/1/2017"))

Making the highlighted adjustments (if you haven't already done that) should work as well.

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.