Addition formulas problem

[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(D812)=0,"N/A",SUM(D812)) 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!!

 

[akphidelt]

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

 

[Kaylen]

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.

 

[Sandy Mann]

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

 

[Kaylen]

Thank you soo much Sandy! It works!

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

 

[Sandy Mann]

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

 

[Kaylen]

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?

 

[Sandy Mann]

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

 

[Kaylen]

You are a genius! The first formula works beautifully!

 

[Sandy Mann]

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

 

[Kaylen]

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?

 

[Sandy Mann]

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.

 

[Kaylen]

You are brilliant! Thank you so much!

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.