| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM - getting #VALUE! when some cells are blank
I need to show the sum of every 14th cell from S20 to DM20. Some of these
cells will be blank. The basic SUM formula I am using is: =SUM(S20+AG20+AU20+BI20+BW20+CK20+CY20+DM20) Due to some of the cells being blank, I am getting a result of #VALUE! How can I get it to still show a value, i.e. to still sum the values that are present? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM - getting #VALUE! when some cells are blank
SUM will handle blank cells so there must be another problem.
I got a result of zero with no data in your range. "Sarah (OGI)" wrote: I need to show the sum of every 14th cell from S20 to DM20. Some of these cells will be blank. The basic SUM formula I am using is: =SUM(S20+AG20+AU20+BI20+BW20+CK20+CY20+DM20) Due to some of the cells being blank, I am getting a result of #VALUE! How can I get it to still show a value, i.e. to still sum the values that are present? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM - getting #VALUE! when some cells are blank
Try...
=SUM(S20,AG20,AU20,BI20,BW20,CK20,CY20,DM20) Hope this helps! In article , Sarah (OGI) wrote: I need to show the sum of every 14th cell from S20 to DM20. Some of these cells will be blank. The basic SUM formula I am using is: =SUM(S20+AG20+AU20+BI20+BW20+CK20+CY20+DM20) Due to some of the cells being blank, I am getting a result of #VALUE! How can I get it to still show a value, i.e. to still sum the values that are present? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM - getting #VALUE! when some cells are blank
=SUM(IF(MOD(COLUMN(S20:DM20),14)=5,S1:DM1))
ctrl+shift+enter "Sarah (OGI)" wrote: I need to show the sum of every 14th cell from S20 to DM20. Some of these cells will be blank. The basic SUM formula I am using is: =SUM(S20+AG20+AU20+BI20+BW20+CK20+CY20+DM20) Due to some of the cells being blank, I am getting a result of #VALUE! How can I get it to still show a value, i.e. to still sum the values that are present? Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM - getting #VALUE! when some cells are blank
should be S20:DM20 instead of S1:DM1 in my previous reply. Here is a correct
version =SUM(IF(MOD(COLUMN(S20:DM20),14)=5,S20:DM20)) "Teethless mama" wrote: =SUM(IF(MOD(COLUMN(S20:DM20),14)=5,S1:DM1)) ctrl+shift+enter "Sarah (OGI)" wrote: I need to show the sum of every 14th cell from S20 to DM20. Some of these cells will be blank. The basic SUM formula I am using is: =SUM(S20+AG20+AU20+BI20+BW20+CK20+CY20+DM20) Due to some of the cells being blank, I am getting a result of #VALUE! How can I get it to still show a value, i.e. to still sum the values that are present? Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM - getting #VALUE! when some cells are blank
(De Ja Vu - I believe you've told me that before for another problem)
I tried my formula in a new spreadsheet and it seemed to work, as did yours. I tried it again in my first spreadsheet but still had a problem. I seem to have got round it now by replacing the '+' with a comma - perhaps because they are not consecutive cells? I'm not sure, but I've managed to get it working. Thanks for your quick response! "Toppers" wrote: SUM will handle blank cells so there must be another problem. I got a result of zero with no data in your range. "Sarah (OGI)" wrote: I need to show the sum of every 14th cell from S20 to DM20. Some of these cells will be blank. The basic SUM formula I am using is: =SUM(S20+AG20+AU20+BI20+BW20+CK20+CY20+DM20) Due to some of the cells being blank, I am getting a result of #VALUE! How can I get it to still show a value, i.e. to still sum the values that are present? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to count blank cells (Rows) between Non-Blank cells in Col "A" | Excel Discussion (Misc queries) | |||
| Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
| How do I skip blank cells when copying over a range of cells? | Excel Discussion (Misc queries) | |||
| HELP: Last row of the column containing blank cells between non-blank cells | Excel Worksheet Functions | |||
| Making Blank Cells Really Blank (Zen Koan) | Excel Worksheet Functions | |||
Linear Mode
