Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
Your formula will fail if there is any non-numeric value in the cells being
added up. "PaulinaDi" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
=SUMPRODUCT(--(MOD(COLUMN(C9:W9),2)=1),C9:W9)
Hope this helps. -- John C "PaulinaDi" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
=SUMPRODUCT(C9:W9,MOD(COLUMN(C9:W9),2))
-- David Biddulph "PaulinaDi" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
Thanks Sheeloo but I wasn´t able to find any non-numeric value in any file.
"Sheeloo" wrote: Your formula will fail if there is any non-numeric value in the cells being added up. "PaulinaDi" wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
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" wrote: =SUMPRODUCT(--(MOD(COLUMN(C9:W9),2)=1),C9:W9) Hope this helps. -- John C "PaulinaDi" wrote: 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
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" wrote: 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" wrote in message ... 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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
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" wrote: =SUMPRODUCT(C9:W9,MOD(COLUMN(C9:W9),2)) -- David Biddulph "PaulinaDi" wrote in message ... 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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
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" wrote: 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" wrote: =SUMPRODUCT(--(MOD(COLUMN(C9:W9),2)=1),C9:W9) Hope this helps. -- John C "PaulinaDi" wrote: 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? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM cells with blank value
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" wrote in message ... 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" wrote: =SUMPRODUCT(C9:W9,MOD(COLUMN(C9:W9),2)) -- David Biddulph "PaulinaDi" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Quantity Blank, Remaining cells in row appear Blank | Excel Worksheet Functions | |||
Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
Making Blank Cells Really Blank (Zen Koan) | Excel Worksheet Functions |