| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Addition formulas problem
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!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Addition formulas problem
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 both conditions can be true. Any help is greatly appreciated!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Addition formulas problem
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!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Addition formulas problem
Thank you soo much Sandy! It works!
"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!! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Addition formulas problem
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 Replace @mailinator.com with @tiscali.co.uk "Kaylen" wrote in message ... Thank you soo much Sandy! It works! "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!! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Addition formulas problem
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!! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Addition formulas problem
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!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Forecast v actual - row addition problem | Excel Worksheet Functions | |||
| Problem with Time Addition | Excel Worksheet Functions | |||
| Excel addition formulas | Excel Discussion (Misc queries) | |||
| Another time addition problem | Excel Worksheet Functions | |||
| Addition problem, number always 2 cents off...Help | Excel Discussion (Misc queries) | |||
Hybrid Mode
