SUM cells with blank value

P

PaulinaDi

I have a table where I need to Sumarize different columns.

Eg:
=C9+E9+G9+I9+K9+M9+O9+Q9+S9+U9+W9 but in this row E9 and M9 have no data as
all these cells have their own formula from other files like
='[2008-dosbocas-porcompr.xls]DosBocas (mat)'!G6, ecc. So, in this case these
E9 and M9 have no values.

The problem is that I am not able to use the first formula as I get a #value
error.
What am I doing wrong?
 
S

Sheeloo

Your formula will fail if there is any non-numeric value in the cells being
added up.
 
T

T. Valko

Try using the SUM function. It will ignore any text entries.

=SUM(C9,E9,G9,I9,K9,M9,O9,Q9,S9,U9,W9)
 
P

PaulinaDi

This formula worked fine but I didn´t understand what ,2)=1 makes to the
range selected. What do these 2 numbers mean?

John C said:
=SUMPRODUCT(--(MOD(COLUMN(C9:W9),2)=1),C9:W9)

Hope this helps.

--
John C


PaulinaDi said:
I have a table where I need to Sumarize different columns.

Eg:
=C9+E9+G9+I9+K9+M9+O9+Q9+S9+U9+W9 but in this row E9 and M9 have no data as
all these cells have their own formula from other files like
='[2008-dosbocas-porcompr.xls]DosBocas (mat)'!G6, ecc. So, in this case these
E9 and M9 have no values.

The problem is that I am not able to use the first formula as I get a #value
error.
What am I doing wrong?
 
P

PaulinaDi

Thanks Sheeloo but I wasn´t able to find any non-numeric value in any file.

Sheeloo said:
Your formula will fail if there is any non-numeric value in the cells being
added up.

PaulinaDi said:
I have a table where I need to Sumarize different columns.

Eg:
=C9+E9+G9+I9+K9+M9+O9+Q9+S9+U9+W9 but in this row E9 and M9 have no data as
all these cells have their own formula from other files like
='[2008-dosbocas-porcompr.xls]DosBocas (mat)'!G6, ecc. So, in this case these
E9 and M9 have no values.

The problem is that I am not able to use the first formula as I get a #value
error.
What am I doing wrong?
 
P

PaulinaDi

I guess I tried this formula before and not sure what happened but right now
it worked fine.
And, to have a blank cell if I have no value on this row? how do I complete
it with the If funcion?

T. Valko said:
Try using the SUM function. It will ignore any text entries.

=SUM(C9,E9,G9,I9,K9,M9,O9,Q9,S9,U9,W9)

--
Biff
Microsoft Excel MVP


PaulinaDi said:
I have a table where I need to Sumarize different columns.

Eg:
=C9+E9+G9+I9+K9+M9+O9+Q9+S9+U9+W9 but in this row E9 and M9 have no data
as
all these cells have their own formula from other files like
='[2008-dosbocas-porcompr.xls]DosBocas (mat)'!G6, ecc. So, in this case
these
E9 and M9 have no values.

The problem is that I am not able to use the first formula as I get a
#value
error.
What am I doing wrong?
 
P

PaulinaDi

Thanks David. This formula seems to be similar to the one John sent to me.
What does the number 2 at the end mean?
And how am I able to use it with the If funcion in order to have a blank
value if I have no data? for example those months still in blank like
October, November and December.

David Biddulph said:
=SUMPRODUCT(C9:W9,MOD(COLUMN(C9:W9),2))
--
David Biddulph

PaulinaDi said:
I have a table where I need to Sumarize different columns.

Eg:
=C9+E9+G9+I9+K9+M9+O9+Q9+S9+U9+W9 but in this row E9 and M9 have no data
as
all these cells have their own formula from other files like
='[2008-dosbocas-porcompr.xls]DosBocas (mat)'!G6, ecc. So, in this case
these
E9 and M9 have no values.

The problem is that I am not able to use the first formula as I get a
#value
error.
What am I doing wrong?
 
T

T. Valko

One way:

=IF(COUNT(C9,E9,G9,I9,K9,M9,O9,Q9,S9,U9,W9),SUM(C9,E9,G9,I9,K9,M9,O9,Q9,S9,U9,W9),"")



--
Biff
Microsoft Excel MVP


PaulinaDi said:
I guess I tried this formula before and not sure what happened but right
now
it worked fine.
And, to have a blank cell if I have no value on this row? how do I
complete
it with the If funcion?

T. Valko said:
Try using the SUM function. It will ignore any text entries.

=SUM(C9,E9,G9,I9,K9,M9,O9,Q9,S9,U9,W9)

--
Biff
Microsoft Excel MVP


PaulinaDi said:
I have a table where I need to Sumarize different columns.

Eg:
=C9+E9+G9+I9+K9+M9+O9+Q9+S9+U9+W9 but in this row E9 and M9 have no
data
as
all these cells have their own formula from other files like
='[2008-dosbocas-porcompr.xls]DosBocas (mat)'!G6, ecc. So, in this case
these
E9 and M9 have no values.

The problem is that I am not able to use the first formula as I get a
#value
error.
What am I doing wrong?
 
J

John C

Read up on what the MOD function does.

Essentially, it is taking columns C through W (which is column numbers 3
through 23), and dividing by 2. If the remainder is 1 (i.e.: an odd column),
then it will be SUMmed, if the remainder is 0 (i.e.: even columns between C &
W, such as D, F, H, etc), then it will be ignored.
--
John C


PaulinaDi said:
This formula worked fine but I didn´t understand what ,2)=1 makes to the
range selected. What do these 2 numbers mean?

John C said:
=SUMPRODUCT(--(MOD(COLUMN(C9:W9),2)=1),C9:W9)

Hope this helps.

--
John C


PaulinaDi said:
I have a table where I need to Sumarize different columns.

Eg:
=C9+E9+G9+I9+K9+M9+O9+Q9+S9+U9+W9 but in this row E9 and M9 have no data as
all these cells have their own formula from other files like
='[2008-dosbocas-porcompr.xls]DosBocas (mat)'!G6, ecc. So, in this case these
E9 and M9 have no values.

The problem is that I am not able to use the first formula as I get a #value
error.
What am I doing wrong?
 
D

David Biddulph

If you want to know what the parameters in the syntax of the MOD function
are, why not try looking at the Excel help for the MOD function?

I don't understand what you are trying to ask for the second part of your
question.
If you are saying that you want a blank result if there are no entries in
the row in question, try
=IF(COUNT(C9:W9)=0,"",SUMPRODUCT(C9:W9,MOD(COLUMN(C9:W9),2)))
--
David Biddulph

PaulinaDi said:
Thanks David. This formula seems to be similar to the one John sent to me.
What does the number 2 at the end mean?
And how am I able to use it with the If funcion in order to have a blank
value if I have no data? for example those months still in blank like
October, November and December.

David Biddulph said:
=SUMPRODUCT(C9:W9,MOD(COLUMN(C9:W9),2))
--
David Biddulph

PaulinaDi said:
I have a table where I need to Sumarize different columns.

Eg:
=C9+E9+G9+I9+K9+M9+O9+Q9+S9+U9+W9 but in this row E9 and M9 have no
data
as
all these cells have their own formula from other files like
='[2008-dosbocas-porcompr.xls]DosBocas (mat)'!G6, ecc. So, in this case
these
E9 and M9 have no values.

The problem is that I am not able to use the first formula as I get a
#value
error.
What am I doing wrong?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top