SUMPRODUCT is OK for a while and later a similar formula returns 0
 

Hello, I'm using =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=3),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=4),53:53) and they work fine. When I go
to =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=5),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=6),53:53) these formulae return zero.
They shouldn't because I'm just working across the columns and there are
numeric values in Columns 5 and 6. I'm a novice Excel user. Thanks in
advance! Pierian Spring

SUMPRODUCT is OK for a while and later a similar formula returns 0
 

That is because the MOD value of MOD(n,5) is 0,1,2,3,4, there is no 5

Try

=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=0),53:53)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pierian Spring" wrote in message
...
Hello, I'm using =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=3),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=4),53:53) and they work fine. When I
go
to =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=5),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=6),53:53) these formulae return zero.
They shouldn't because I'm just working across the columns and there are
numeric values in Columns 5 and 6. I'm a novice Excel user. Thanks in
advance! Pierian Spring

SUMPRODUCT is OK for a while and later a similar formula retur
 

Hi Bob, you probably recognise your own formula from yesterday! I tried your
recommendation below in Column E (which is 5) and it worked. However, how do
I move it on to Column F? I need to SUM ebery 5th Column. I thought you
indicated that 2 SUMS B, G, etc. So if I modifiy 2 . . . . .? Sorry for being
a nuisance.

"Bob Phillips" wrote:

That is because the MOD value of MOD(n,5) is 0,1,2,3,4, there is no 5

Try

=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=0),53:53)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pierian Spring" wrote in message
...
Hello, I'm using =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=3),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=4),53:53) and they work fine. When I
go
to =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=5),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=6),53:53) these formulae return zero.
They shouldn't because I'm just working across the columns and there are
numeric values in Columns 5 and 6. I'm a novice Excel user. Thanks in
advance! Pierian Spring

SUMPRODUCT is OK for a while and later a similar formula retur
 

Pierian,

the column being summed is determine by the value being compared against, =0
is columns E, J etc., =1 is F, K, etc. and so on, so just adjust that part
of the formula.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pierian Spring" wrote in message
...
Hi Bob, you probably recognise your own formula from yesterday! I tried
your
recommendation below in Column E (which is 5) and it worked. However, how
do
I move it on to Column F? I need to SUM ebery 5th Column. I thought you
indicated that 2 SUMS B, G, etc. So if I modifiy 2 . . . . .? Sorry for
being
a nuisance.

"Bob Phillips" wrote:

That is because the MOD value of MOD(n,5) is 0,1,2,3,4, there is no 5

Try

=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=0),53:53)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pierian Spring" wrote in
message
...
Hello, I'm using =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=3),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=4),53:53) and they work fine. When
I
go
to =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=5),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=6),53:53) these formulae return
zero.
They shouldn't because I'm just working across the columns and there
are
numeric values in Columns 5 and 6. I'm a novice Excel user. Thanks in
advance! Pierian Spring

SUMPRODUCT is OK for a while and later a similar formula retur
 

Thanks Bob, it worked a treat. All my formulae are now in place. Now I just
need to start populating the ss! Many thanks for your help! Pierian

"Bob Phillips" wrote:

Pierian,

the column being summed is determine by the value being compared against, =0
is columns E, J etc., =1 is F, K, etc. and so on, so just adjust that part
of the formula.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pierian Spring" wrote in message
...
Hi Bob, you probably recognise your own formula from yesterday! I tried
your
recommendation below in Column E (which is 5) and it worked. However, how
do
I move it on to Column F? I need to SUM ebery 5th Column. I thought you
indicated that 2 SUMS B, G, etc. So if I modifiy 2 . . . . .? Sorry for
being
a nuisance.

"Bob Phillips" wrote:

That is because the MOD value of MOD(n,5) is 0,1,2,3,4, there is no 5

Try

=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=0),53:53)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pierian Spring" wrote in
message
...
Hello, I'm using =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=3),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=4),53:53) and they work fine. When
I
go
to =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=5),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=6),53:53) these formulae return
zero.
They shouldn't because I'm just working across the columns and there
are
numeric values in Columns 5 and 6. I'm a novice Excel user. Thanks in
advance! Pierian Spring