Adding NonBlank Cells
 

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?

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

"Emily8" wrote in message
...
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?

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


In article ,
Emily8 wrote:

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?

Emily8 wrote:
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+AE1 6
....

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)