Adding NonBlank Cells

[Guest]

I have a user who is trying to add a number of different cells toghehter.
Sometimes they have a value and sometimes they are blank. The formula is
evulating to #Value!.

This is the formula:
I16+K16+M16+O16+Q16+S16+U16+W16+Y16+AA16+AC16+AE16

I tried changing it to this:
(IF(ISBLANK(I18), 0, I18))+ IF(ISBLANK(K18), 0, K18)+ IF(ISBLANK(M18), 0,
M18)+ IF(ISBLANK(O18), 0, O18)+ IF(ISBLANK(Q18), 0, Q18)+ IF(ISBLANK(S18), 0,
S18)+ IF(ISBLANK(U18), 0, U18)+ IF(ISBLANK(W18), 0, W18)+ IF(ISBLANK(Y18), 0,
Y18)+ IF(ISBLANK(AA18), 0, AA18)+ IF(ISBLANK(AC18), 0, AC18)+
IF(ISBLANK(AE18), 0, AE18)

I am still getting the error. What am I doing wrong?

 

[Bernie Deitrick]

Emily,

Use

=SUM(I16,K16,M16,O16,Q16,S16,U16,W16,Y16,AA16,AC16,AE16)

Sum ignores strings, which adding doesn't. One of your cells has a
non-number (text), even if it looks like a number.

You may want to format your cells as general, and re-enter your values to
eliminate the text.

HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

Your user is "blanking" cells by using the spacebar, which evaluates to
text, not blank, and the + operator throws the #VALUE! error if an
argument is Text.

You could use something like:

=IF(I18<>"",I18)+IF(K18<>"",K18)...

or, since you're adding every other cell:

=SUMPRODUCT(--(MOD(COLUMN(I18:AE18),2)=1),I18:AE18)




For an explanation of the usage of "--", see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[hrlngrv]

I have a user who is trying to add a number of different cells toghehter.
Sometimes they have a value and sometimes they are blank. The formula is
evulating to #Value!.

This is the formula:
I16+K16+M16+O16+Q16+S16+U16+W16+Y16+AA16+AC16+AE16

....

As others have already pointed out, the cells that appear blank almost
certainly contain text, and you can't use text that doesn't look like
numbers as operands for arithmetic operators. Yet another work around
(though Bernie's approach would be best),

=N(I16)+N(K16)+N(M16)+N(O16)+N(Q16)+N(S16)+N(U16)+N(W16)+N(Y16)+N(AA16)
+N(AC16)+N(AE16)