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

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

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

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

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

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