Ignore NA in sumif

[amartino44]

Hi. If I use a sumif formula and one of the cells that is summed has a NA, then the sumif total comes out NA. How can I ignore this cell in the sumif total? Thanks

[Hang Glider]

=if(iserror(sumif xxxxxxx),"")
where (sumif xxxxxx) is your sumif formula


ahh - strike that - re-read your post. This will leave the whole sum blank, not just ignore the errant cell.

[daddylonglegs]

instead of

=SUMIF(A:A,"x",B:B)

use SUMIFS like this

=SUMIFS(B:B,A:A,"x",B:B,"<>#n/a")

[amartino44]

I am using sumifs. This is an example =SUMIFS($D35:$BX35,$D$4:$BX$4,CR$4,$D$6:$BX$6,CR$6). What does the "<>#n/a" do?

[amartino44]

Also, is there any way to get around this without creating a sumifs (keeping just the sumif equation)?

[daddylonglegs]

You'll need to use SUMIFS for more than one condition (there are other possible approaches but not SUMIF and none better in general).

In that SUMIFS formula I assume that you have #N/A errors in the sum range, to ignore those you can add another condition, i.e.

=SUMIFS($D35:$BX35,$D35:$BX35,"<>#N/A",$D$4:$BX$4,CR$4,$D$6:$BX$6,CR$6)

"<>#N/A" means "Not equal to #N/A"