Addition formulas problem

  • Thread starter Thread starter Kaylen
  • Start date Start date
K

Kaylen

For example:
addition formula: A+B+C=D
Condition 1: If A=B=C=N/A, shows N/A since all = N/A
Condition 2: If A=B=C=0, the shows 0 since 0+0+0+0+0=0
Condition 3: If A=0, B=0, C=0, but D= N/A, shows 0 since A+B+C=0
Condition 4: If A=N/A, B=N/A, C=N/A, but C=0, shows 0
Condition 5: If A=4, B=4, C=4, D=N/A, shows 12 since 4+4+4=12
Condition 6: If A= 4, B=4, C= 0, D=N/A, shows 8 since 4+4+0=8
Etc...

Problem:
If example formula is =SUM(E8:E12), then if all(E8 to E12) are N/A then it
shows 0( I want it to show N/A if all is N/A) --- only condition 2 is met

If example formula is =IF(SUM(D8:D12)=0,"N/A",SUM(D8:D12)) then when all is
0, it shows N/A( I want it to shows 0 if all is 0)----only condition 1 is met

I am looking for a formula where both conditions can be true.

Any help is greatly appreciated!!
 
This is untested but you could try using count and counta to determine if
there is any text in the series. For instance the formula might be

=IF((COUNTA(A1:C1)-COUNT(A1:C1))=0,SUM(A1:C1),"NA")

Basically count, counts all numerical values. Counta counts everything
 
Based on your suggestion, this is the formula:
=IF((COUNTA(E8:E12)-COUNT(E8:E12))=0, SUM(E8:E12),"N/A")

Both conditions 1 and 2 are met with this formula, BUT, however, if one of
the values is N/A, for example B=N/A, the result shows N/A instead of summing
the other values. Like for conditions 4, 5, and 6, the results are N/A which
suppose to showing the sum. Is there a forumla where all conditions can be
true?

Thank you so much for you effort.
 
I understand your conditions to be if all entries, (which may not be all
five cells), are N/A then return N/A otherwise return the SUM() of E8:E12,
if so then try:

=IF(COUNTIF(E8:E12,"n/a")=COUNTA(E8:E12),"N/A",SUM(E8:E12))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thank you soo much Sandy! It works!

Sandy Mann said:
I understand your conditions to be if all entries, (which may not be all
five cells), are N/A then return N/A otherwise return the SUM() of E8:E12,
if so then try:

=IF(COUNTIF(E8:E12,"n/a")=COUNTA(E8:E12),"N/A",SUM(E8:E12))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
You are very welcome, thanks for letting us know that it worked.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
I tried the formula with a range of cells and it works, but for the ones that
involve a certain cell, I keep getting error message.

This is the formula I entered based on your suggestion:
=IF(COUNTIF(E18:E22,E14,"N/A")=COUNTA(E18:E22,E14),"N/A",SUM(E18:E22,E14))

I get an error message saying that the formula has too many aruguments. Can
this forumla be fixed?
 
Try:

=IF(COUNTIF(E18:E22,"N/A")+(E14="N/A")=COUNTA(E18:E22,E14),"N/A",SUM(E18:E22,E14))

This returns N/A for all empty cell then try:

=IF((COUNTA(E18:E22)=0)+(E14="")=2,"",IF(COUNTIF(E18:E22,"N/A")+(E14="N/A")=COUNTA(E18:E22,E14),"N/A",SUM(E18:E22,E14)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Glad that we got there in the end.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
I'm sorry but there is an error message saying too many arguments again when
I tried this formula:

=IF(COUNTIF(E31="N/A")+(E34="N/A")+(E37="N/A")+(E40="N/A")=COUNTA(E31,E34,E37,E40),"N/A",SUM(E31,E34,E37,E40))

Can it fixed?
 
My ISP has stopped reading Newsgroups at the moment for some reason so I am
having to post through the Communities site.

COUNTIF() only takes one range in its first argument. Take the COUNTIF()
out and you have:

=IF((E31="N/A")+(E34="N/A")+(E37="N/A")+(E40="N/A")=COUNTA(E31,E34,E37,E40),"N/A",SUM(E31,E34,E37,E40))

The four comparisons, (E31="N/A") etc., return TRUE or FALSE which when
added up with the + sign, XL changes into 1 or 0. This sum is then compared
with the COUNTA() return.
 
You are brilliant! Thank you so much!

Sandy Mann said:
My ISP has stopped reading Newsgroups at the moment for some reason so I am
having to post through the Communities site.

COUNTIF() only takes one range in its first argument. Take the COUNTIF()
out and you have:

=IF((E31="N/A")+(E34="N/A")+(E37="N/A")+(E40="N/A")=COUNTA(E31,E34,E37,E40),"N/A",SUM(E31,E34,E37,E40))

The four comparisons, (E31="N/A") etc., return TRUE or FALSE which when
added up with the + sign, XL changes into 1 or 0. This sum is then compared
with the COUNTA() return.
Click to expand...
 
Back
Top