About Us

Kaylen

You are brilliant! Thank you so much!

"Sandy Mann" wrote:

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" wrote:

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" wrote:

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(E 18: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

Replace @mailinator.com with @tiscali.co.uk

"Kaylen" wrote in message
...
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" wrote:

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

Replace @mailinator.com with @tiscali.co.uk

"Kaylen" wrote in message
...
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.

"akphidelt" wrote:

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" wrote:

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 all conditions can be true.

Any help is greatly appreciated!!

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Forecast v actual - row addition problem	Tom Sharrocks	Excel Worksheet Functions	2	October 31st 07 06:49 PM
Problem with Time Addition	Steve M	Excel Worksheet Functions	5	March 19th 07 07:18 PM
Excel addition formulas	pippawl	Excel Discussion (Misc queries)	3	September 6th 06 11:01 PM
Another time addition problem	Martin B	Excel Worksheet Functions	2	April 14th 06 08:26 PM
Addition problem, number always 2 cents off...Help	WTG	Excel Discussion (Misc queries)	3	March 30th 05 01:57 PM

Menu

About Us