SUMIF formula to ignore #N/A

[walt1122]

Hi All I believe Juan Pablo posed a quick little diddy on how to have the SUMIF ignore #N/A?? I promised myself I would remember it but here I am asking if anyone remembers it or know how to have Excel add up a column even though the column contains #N/A's.

thanks

Walt

 

[Mark W.]

=SUMIF(range,"<>#N/A")

 

[Aladin Akyurek]

=SUMIF(Range,"<>#N/A")

 

[walt1122]

that's got it. I left out the " "'s

thanks

Walt

 

[Mark W.]

that's got it. I left out the " "'s

thanks

Walt

You might want to review the examples of criteria in the Excel Help topic for the SUMIF worksheet function.

 

[fg2009]

I tried the formula above and it works. However, when I add an extra calc within the SUMIF it rejects it.

This is my variation:

=sumif((+A1-SUM(A2:A4)),"<>#N/A")

Any suggestions?

Thx,
FG

 

[barry houdini]

Try

=A1-SUMIF(A2:A4,"<>#N/A")

or if A1 could be #N/A

=IF(ISNUMBER(A1),A1)-SUMIF(A2:A4,"<>#N/A")

 

[fg2009]

It works. Thank you sir.

FG

 

[Catherine1984]

Hi Guys

Im all new to this, but ive read the above thread and i think the formula is relavent to my problem.

Im using the SUMIF Function but i want the formula to ignore certain cells

so at the moment i have this

=SUMIF('SALES 2009'!T4:T116,F1,'SALES 2009'!R4:R116)

But i want this formula to ignore any sales that have not been invoiced yet, so the second criteria would be TO BE INVOICED

I read somewhere that using formula SUMIFS you could stipulate two or more criterias but is isnt avaiable Excel 2003!

Hope you guys can help me out as im going round the bend!!!

 

[barry houdini]

Hello Catherine, welcome to MrExcel, you'd probably get a better response if you started your own question.....

You can use SUMPRODUCT for counting or summing with multiple criteria, e.g.

=SUMPRODUCT(--('SALES 2009'!T4:T116=F1),--('SALES 2009'!S4:S116<>"To be invoiced"),'SALES 2009'!R4:R116)

I'm assuming that your "To be invoiced" text would be in column S, so this formula will sum all sales in column R where the T column value is equal to F1 but S column doesn't equal "To be invoiced"