adding non consecutive cells

[Joe]

I an working on a spreadsheet and I need to add multiple
non consecutive cells that exceed the limit allowed in
the formula. Any suggestions on a formula or a way to
reconfigure the spreadsheet so that I would be able to do
this.

example: I have to add together every 5th cell in a
column from rows a5 to a250.

Any thoughts,

Thanks in advance

 

[Paul]

I an working on a spreadsheet and I need to add multiple
non consecutive cells that exceed the limit allowed in
the formula. Any suggestions on a formula or a way to
reconfigure the spreadsheet so that I would be able to do
this.

example: I have to add together every 5th cell in a
column from rows a5 to a250.

Any thoughts,

Thanks in advance

You could use:
=SUMPRODUCT((A1:A250)*(MOD(ROW(A1:A250),5)=0))

 

[Andy B]

Hi

One way would be to define a name for the cells, and use =SUM(name)

Andy.

 

[Ken Wright]

One way:-

=SUMPRODUCT((A5:A250)*(MOD(ROW(A5:A250),5)=0))

Note:-

=MOD(ROW(A5:A250),5) uses the MOD function to divide the row number of each row
by 5 and the return the remainder. This =0 bit means that that part of the
formula will set up an array of TRUE/FALSEs, eg

TRUE
FALSE
FALSE
FALSE
FALSE
TRUE
FALSE

These are interpreted as 1s (TRUE) and 0s (FALSE) by Excel. It then multiplies
the values in Col A by these 1s and 0s, effectively giving you only the ones on
the rows that are divisible by 5 with no remainder (Every 5th row in this case),
and then sums the results.

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(MOD(ROW(A5:A250),5)=0),A5:A250)

you can also over come the 30 limit using sum by using something like

=SUM((A5,A10,A15,A20,A25,A30,A35,A40,A45,A50,A55,A60,A65,A70,A75,A80,A85,A90
,A95,A100,A105,A110,A115,A120,A125,A130,A135,A140,A145),A150,A155,A160,A165
and so on)

 

[Andrew]

Excuse me for butting in here, but what is the meaning of the 2 minus
signs in this formula?

=SUMPRODUCT(--(MOD(ROW(A5:A250),5)=0),A5:A250)

I tried this just one minus sign,
=SUMPRODUCT(-(MOD(ROW(A5:A250),5)=0),A5:A250), and got a negative
number.

Then I tried =SUMPRODUCT((MOD(ROW(A5:A250),5)=0),A5:A250) and got
zero.

How does this work? Could you explain it to me?

Also SUMPRODUCT seems to be acting similar to IF or am I mistaken?

Please explain. I'm just a newbie but I want to understand

 

[Peo Sjoblom]

See

http://tinyurl.com/25hyf

 

[Ken Wright]

See if this helps for the SUMPRODUCT explanation:-

The formula exploits the fact that Excel interprets TRUE as 1 and FALSE
as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A")*(C9:C20))

This sets up an array that gives you something that looks like this
(depending on the variables of course):-

A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------------
35

and the SUM bit just adds up all the end values of the products



If you exclude the last part of the formula, so that it becomes:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A"))

then what you end up with is a sum of a bunch of TRUE/FALSE values depending on
whether or not the criteria has been met on that row, and this is the same as
counting the number of records that meet your criteria. Imagine the above
tables without Column C, and the last one would look like the following:-

A B
9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1