SUMIF with #N/A

[MrRJ]

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

 

[Bob Phillips]

Do you mean

=IF(COUNTIF('SouceA'!$AG$2:$AG$918,$B47)=0,"No
pricing",SUMIF('SouceA'!$AG$2:$AG$918,$B47,'SourceA'!$AL$2:$AL$918))

 

[Pete_UK]

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

 

[MrRJ]

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

 

[MrRJ]

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

 

[MrRJ]

Never mind, I think I figured it out. My mind is going.

 

[Bob Phillips]

Well just shout if you need any more.