Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with #N/A
I am trying to resolve a simple problem. I do not want to see the return
value of #N/A because it interupts when I do a sumation. I am using this formula, which is part of another formula that I am using. This is the part this is giving me grief. =SUMIF('SouceA'!$AG$2:$AG$918,$B47,'SourceA'!$AL$2 :$AL$918) Note that I am looking for a value in SourceA but it is not there. Which is ok. I want to return a value or a statement to something like this "No pricing" if there is no value. Because it is those that I will be looking for in my analysis. Thanks, RJ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with #N/A
Do you mean
=IF(COUNTIF('SouceA'!$AG$2:$AG$918,$B47)=0,"No pricing",SUMIF('SouceA'!$AG$2:$AG$918,$B47,'Source A'!$AL$2:$AL$918)) -- __________________________________ HTH Bob "MrRJ" wrote in message ... I am trying to resolve a simple problem. I do not want to see the return value of #N/A because it interupts when I do a sumation. I am using this formula, which is part of another formula that I am using. This is the part this is giving me grief. =SUMIF('SouceA'!$AG$2:$AG$918,$B47,'SourceA'!$AL$2 :$AL$918) Note that I am looking for a value in SourceA but it is not there. Which is ok. I want to return a value or a statement to something like this "No pricing" if there is no value. Because it is those that I will be looking for in my analysis. Thanks, RJ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with #N/A
You need to go back to the formulae which are returning the #N/A
errors in the first place. If these are VLOOKUP or MATCH type formulae, you need to change them to something like: =IF(ISNA(your_formula),"",your_formula) which will return a blank instead of the error. Hope this helps. Pete On Oct 29, 2:56*pm, MrRJ wrote: I am trying to resolve a simple problem. *I do not want to see the return value of #N/A because it interupts when I do a sumation. *I am using this formula, which is part of another formula that I am using. *This is the part this is giving me grief. =SUMIF('SouceA'!$AG$2:$AG$918,$B47,'SourceA'!$AL$2 :$AL$918) Note that I am looking for a value in SourceA but it is not there. *Which is ok. *I want to return a value or a statement to something like this "No pricing" if there is no value. *Because it is those that I will be looking for in my analysis. Thanks, RJ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with #N/A
Thanks Bob,
I will give this a shot. I am working on something else. I will try it after lunch. What you are saying makes sense because if my sourceA file does not have the data, then show me "no pricing", else. The logic is there. Thanks again, RJ "Bob Phillips" wrote: Do you mean =IF(COUNTIF('SouceA'!$AG$2:$AG$918,$B47)=0,"No pricing",SUMIF('SouceA'!$AG$2:$AG$918,$B47,'Source A'!$AL$2:$AL$918)) -- __________________________________ HTH Bob "MrRJ" wrote in message ... I am trying to resolve a simple problem. I do not want to see the return value of #N/A because it interupts when I do a sumation. I am using this formula, which is part of another formula that I am using. This is the part this is giving me grief. =SUMIF('SouceA'!$AG$2:$AG$918,$B47,'SourceA'!$AL$2 :$AL$918) Note that I am looking for a value in SourceA but it is not there. Which is ok. I want to return a value or a statement to something like this "No pricing" if there is no value. Because it is those that I will be looking for in my analysis. Thanks, RJ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with #N/A
Bob,
I am back at it now. I did this formula on another project that I was able to use it for. It was a success. However, for the project that I am currently working on, it does not. I wonder if there are specific criteria's that it is required to be successful? I did simple countif to see what it is doing. Where it should be giving me zero, it is giving me 1, not sure why? I am baffled now. Any suggestions? RJ "Bob Phillips" wrote: Do you mean =IF(COUNTIF('SouceA'!$AG$2:$AG$918,$B47)=0,"No pricing",SUMIF('SouceA'!$AG$2:$AG$918,$B47,'Source A'!$AL$2:$AL$918)) -- __________________________________ HTH Bob "MrRJ" wrote in message ... I am trying to resolve a simple problem. I do not want to see the return value of #N/A because it interupts when I do a sumation. I am using this formula, which is part of another formula that I am using. This is the part this is giving me grief. =SUMIF('SouceA'!$AG$2:$AG$918,$B47,'SourceA'!$AL$2 :$AL$918) Note that I am looking for a value in SourceA but it is not there. Which is ok. I want to return a value or a statement to something like this "No pricing" if there is no value. Because it is those that I will be looking for in my analysis. Thanks, RJ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with #N/A
Never mind, I think I figured it out. My mind is going.
"Bob Phillips" wrote: Do you mean =IF(COUNTIF('SouceA'!$AG$2:$AG$918,$B47)=0,"No pricing",SUMIF('SouceA'!$AG$2:$AG$918,$B47,'Source A'!$AL$2:$AL$918)) -- __________________________________ HTH Bob "MrRJ" wrote in message ... I am trying to resolve a simple problem. I do not want to see the return value of #N/A because it interupts when I do a sumation. I am using this formula, which is part of another formula that I am using. This is the part this is giving me grief. =SUMIF('SouceA'!$AG$2:$AG$918,$B47,'SourceA'!$AL$2 :$AL$918) Note that I am looking for a value in SourceA but it is not there. Which is ok. I want to return a value or a statement to something like this "No pricing" if there is no value. Because it is those that I will be looking for in my analysis. Thanks, RJ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with #N/A
Well just shout if you need any more.
-- __________________________________ HTH Bob "MrRJ" wrote in message ... Never mind, I think I figured it out. My mind is going. "Bob Phillips" wrote: Do you mean =IF(COUNTIF('SouceA'!$AG$2:$AG$918,$B47)=0,"No pricing",SUMIF('SouceA'!$AG$2:$AG$918,$B47,'Source A'!$AL$2:$AL$918)) -- __________________________________ HTH Bob "MrRJ" wrote in message ... I am trying to resolve a simple problem. I do not want to see the return value of #N/A because it interupts when I do a sumation. I am using this formula, which is part of another formula that I am using. This is the part this is giving me grief. =SUMIF('SouceA'!$AG$2:$AG$918,$B47,'SourceA'!$AL$2 :$AL$918) Note that I am looking for a value in SourceA but it is not there. Which is ok. I want to return a value or a statement to something like this "No pricing" if there is no value. Because it is those that I will be looking for in my analysis. Thanks, RJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf | Excel Discussion (Misc queries) | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |