Sum excluding n/a's

[timarcarze]

I have a similar query to this post, but still having issues with it.

Basically I am wanting to sum cells J13,J21,J26,J34,J41,J44, however cell J13 has a #N/A, therefore when I sum those cells it still appears as #N/A.

The formula I am using is:
=IF(OR(J13="",J21="",J26="",J34="",J41="",J44=""),"",SUM(IF(J13&J21&J26&J34&J41&J44,"<>#N/A")))

Does anyone know how to fix this?

Thanks
Tim

[martindwilson]

i'd fix the n/a bit !

[timarcarze]

Hi Martin

I actually need to include the N/A bit, as it links to a chart, e.g. NA(), so it doesn't plot on graph.

Any one else know how I can fix?

[timarcarze]

Alternatively, I have worked a way around the previous post problem by using following formula:

=SUMIF(J45:J50,"<>#N/A")

This works fine and adds up J45:J50, while excluding #N/A's.

However, as soon as I place the following IF statement in front of it:

=IF(J5="","",SUMIF(J45:J50,"<>#N/A"))

It returns #N/A????

Does anyone know why? Or how to fix this?

Thanks very much!

[timarcarze]

Hi!
Does anyone have any suggestions on above posts???
Thanks
T

[martindwilson]

you was coloured in RED so i had to check with admin
i can't get that to fail as you describe
i tried
=IF(A2="","",SUMIF(B1:H1,"<>#N/A")) and deleberatly put a vlook up returning #n/a in cell d1
it totaled b1:h1 nicely as soon as i put value in a2

[timarcarze]

Thanks Martin.

Strange.... I think it might have something to do with my N/A cell having an error in it..... Oh wells.

Cheers
T

[Cheeky Charlie]

Martin, you can force an N/A error very easily... =na()

Tim, to confirm, I also found your formula as tested by Martin works perfectly for me too... the problem is at your end