N/A Question

[DDH]

When I create a line chart and I don't want the line to bottom out, I use.

IF(B2<1,#N/A,SUM(B2:C2)) the #N/A and it works.

But if I use IF(J26<1,#N/A,J26-J25) the column total
will not add because of the N/A.

Thank you for the help.

 

[Aladin Akyurek]

When I create a line chart and I don't want the line to bottom out, I use.

IF(B2<1,#N/A,SUM(B2:C2)) the #N/A and it works.

But if I use IF(J26<1,#N/A,J26-J25) the column total
will not add because of the N/A.

Thank you for the help.

You did not say which column...

Either:

=SUM(SUMIF($F$2:$F$400,{"<0",">0"}))

Or:

=SUMIF($F$2:$F$400,"<"&9.99E+307)

would succeed to sum F2:F400 having #N/A's.

 

[DDH]

Thank you Aladin Akyurek for your reply.
It work very well.
I forgot that I had two questions and I only put one down.
I was useing SUNPRODUCT in another area.

SUMPRODUCT(($G$8:$G$38=W8)*($K$8:$K$38))

and with the #N/A it will not work.
I tried SUMPRODUCT(($G$8:$G$38=W8)*($K$8:$K$38,{"<0",">0"}))
and it did not work.
Thank you for your help.

 

[Aladin Akyurek]

Thank you Aladin Akyurek for your reply.
It work very well.

You are welcome.

I forgot that I had two questions and I only put one down.
I was useing SUNPRODUCT in another area.

SUMPRODUCT(($G$8:$G$38=W8)*($K$8:$K$38))

and with the #N/A it will not work.
I tried SUMPRODUCT(($G$8:$G$38=W8)*($K$8:$K$38,{"<0",">0"}))
and it did not work.
Thank you for your help.

If on 2007 or later...

=SUM(SUMIFS($K$8:$K$38,$K$8:$K$38,{"<0",">0"},$G$8:$G$38,W8))

Otherwise, control+shift+enter, not just enter:

=SUM(IF($G$8:$G$38=W8,IF(ISNUMBER($K$8:$K$38),$K$8:$K$38)))

 

[DDH]

Thank you Aladin Akyurek for your reply.
I used the folrmula SUM(SUMIFS($K$8:$K$38,$K$8:$K$38,{"<0",">0"},$G$8:$G$38,W8))
and it worked perfect.
Thank you very much.

Why is the ($K$8:$K$38,$K$8:$K$38 wrote twice?
This is the value look up, the "G" is the look up column and the "W" is what it
is looking for.

Thank you again for your help and knowledge.
ddh

 

[Aladin Akyurek]

Thank you Aladin Akyurek for your reply.
I used the folrmula SUM(SUMIFS($K$8:$K$38,$K$8:$K$38,{"<0",">0"},$G$8:$G$38,W8))
and it worked perfect.
Thank you very much.

You are welcome.

Why is the ($K$8:$K$38,$K$8:$K$38 wrote twice?

K8:K38 is both a range to sum and a criteria/condition range for which {"<0",">0"} must hold. The {"<0",">0"} allows the formula to skip #N/A's in the sum range.

This is the value look up, the "G" is the look up column and the "W" is what it is looking for.

G8:G36 is also a criteria/condition range for which W8 must hold.

Thank you again for your help and knowledge.
ddh

Thanks for providing feedback.