SUM cells with blank value

[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?

 

[Sheeloo]

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

 

[John C]

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

Hope this helps.

 

[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)

 

[David Biddulph]

=SUMPRODUCT(C9:W9,MOD(COLUMN(C9:W9),2))

 

[PaulinaDi]

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

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

Hope this helps.

--
John C

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?

 

[PaulinaDi]

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

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

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?

 

[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?

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

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?

 

[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.

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

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. 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

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?

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

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?

 

[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

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

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

Hope this helps.

--
John C

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?

 

[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

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.

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

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?