Hi,

Try this for summing with NAs in the range

=SUMIF(J4:J14,"<"&10^23)

or this

=SUMIF(J4:J14,"<#N/A")

Mike



"Ang" wrote:

Hi,

Please help, I have tried hard to rectify this using some different formula
etc but to no avail.

I have a spreadsheet which logs visits to customers so that we can get a %
of visits which become orders, however we need this by
Team/Individual/Product etc, so gets more intense. I have broken it down as
far as possible to collate the data.

I am using formula: =IF((AND(F4="Education",H40)),H4,0) to get the value of
orders/team.

i.e. if F4 is the Education Team and a figure is in order column over zero
then put the figure (as this is an order).

The Team column (i.e. Education) comes from a VLOOKUP table, so therefore
they select staff initials (from drop down list) and it automatically looks
up the team name.

This works great until the blank lines, no initials selected, results in no
team name (#n/a) and #N/A's in my result - This wouldn't matter only I
cannot add up a column with them appearing, result in AutoSum is (yes you
guessed it!) #N/A!!

Am I missing something glaringly obvious? Please help, I'm so close to
finishing this one!

I have tried =if(ISNA..... but cannot seem to get it to work,. not sure if I
am using this in the correct place.
--
Ang.