Adding NonBlank Cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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
 
Emily8 said:
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
Click to expand...
....

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)
 
Back
Top