Help on SUMIF please!

  • Thread starter Thread starter boxcarracer15
  • Start date Start date
B

boxcarracer15

Hi,
I have a spreadsheet where I am trying to insert a function in a cel
which would say:
'Display the sum of cells B5, C8 and B10. If the value contains th
NA() function, discount it and display the sum of the other values.'
I have tried using SUMIF but cannot get the cell to display correctly
what should I enter for this function?
Thanks
 
You probably have to use something like

=IF(ISNA(B5),0,B5)+IF(ISNA(C8),0,C8)+IF(ISNA(B10),0,B10)

given that you use NA(), if the #N/A comes from an error in a formula and
not willingly you could trap that particular formula with ISNA

You could also use SUMIF like

=SUMIF(B5,"<>#N/A")+SUMIF(C8,"<>#N/A")+SUMIF(B10,"<>#N/A")

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Its a bit kludgy, but Excel seems to treat all numbers as greater than #N/A, so the following would work:

=sumif(B5,">-99999999")+sumif(C8,">-99999999")+sumif(B10,">-99999999")
provided all of your numbers will always be >-99999999. Any number that will always be smaller than your numbers would do, so if they are always positive, then your criteria could be ">0"
 
Back
Top