Selecting evenly spaced cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 17 Sep 2007 16:48 GMT

Hello,

I have data set up such that I need to sum the value of a certain cell
and all its offsets

i.e.

I have data in B2, B12, B22, B32, B42 and I want to sum up only these
values, however I don't want to explicitly state each cell. For my
other summation I want to sum up B3, B13, B23, B33, B43 and etc.

What would this group suggest be the most elegant way in doing so

Reply to this Message

Chris - 17 Sep 2007 16:59 GMT

Let me try to further explain

My worksheet has this sort of setup

Summary
A's - Summed Up Value
B's - Summed Up Value
C's - Summed Up Value

Issue 1
A - 5
B - 4
C - 2

Issue 2
A - 10
B - 5
C - 9

Issue 3
A - 1
B - 2
C - 3

What I want is to sum up all the A's, B's, and C's that are evenly
distributed and put them in the cells labeled "Summed Up Value"

> Hello,
>
[quoted text clipped - 8 lines]
>
> What would this group suggest be the most elegant way in doing so

Reply to this Message

Sandy Mann - 17 Sep 2007 17:10 GMT

This will sum all cells in B3:B123 spaced 10 rows apart, ie 3, 13, 23, 33,
43 etc.

=SUM(IF(MOD(ROW(B1:B123),10)=3,B1:B123))

It is an array formula so must be entered with Ctrl + Shift + Enter instead
of just Enter.

Adjust to your own needs.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Hello,
>
[quoted text clipped - 8 lines]
>
> What would this group suggest be the most elegant way in doing so

Reply to this Message

Chris - 17 Sep 2007 17:48 GMT

Ok,

I can see how that can work, but say rather than values for A,B,C i
have strings which in which I use a VLOOKUP to get teh value of them.

I tried something like
=SUM(IF(MOD(ROW(E1:E123),10)=5,VLOOKUP(E1:E123, Values,2)))

But that doesn't seem to work

> This will sum all cells in B3:B123 spaced 10 rows apart, ie 3, 13, 23, 33,
> 43 etc.
[quoted text clipped - 28 lines]
>
> > What would this group suggest be the most elegant way in doing so

Reply to this Message

Sandy Mann - 17 Sep 2007 18:20 GMT

Not every finction will work with arrays and I think that you are out of
luck unless some of the clever people can come up with something for you.

Signature

Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Ok,
>
[quoted text clipped - 39 lines]
>>
>> > What would this group suggest be the most elegant way in doing so

Reply to this Message

Peo Sjoblom - 17 Sep 2007 18:36 GMT

If the OP wants to sum values in B3:B123 at each 12th row where E3:E123 is a
particular string why not

=SUMPRODUCT(--(MOD(ROW(E1:E123),10)=3),--(E1:E123="abc"),B1:B123)

where the string in this case is "abc"

Signature

Regards,

Peo Sjoblom

> Not every finction will work with arrays and I think that you are out of
> luck unless some of the clever people can come up with something for you.
[quoted text clipped - 42 lines]
>>>
>>> > What would this group suggest be the most elegant way in doing so

Reply to this Message

Chris - 17 Sep 2007 18:39 GMT

I think I might have to code up a UDF

Thanks for the help!

> Not every finction will work with arrays and I think that you are out of
> luck unless some of the clever people can come up with something for you.
[quoted text clipped - 56 lines]
>
> >> > What would this group suggest be the most elegant way in doing so

Reply to this Message

Ron Coderre - 17 Sep 2007 18:43 GMT

Using your sample formula as the base....

This regular formula:
=SUMPRODUCT(--(MOD(ROW(E1:E123),10)=5),SUMIF(ValuesCol_1,E1:E123,ValuesCol_2))

Where:
ValuesCol_1 is the first column of your Values range
ValuesCol_2 is the second column of your Values range

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

> Ok,
>
[quoted text clipped - 39 lines]
>>
>> > What would this group suggest be the most elegant way in doing so

Reply to this Message

Gary''s Student - 17 Sep 2007 17:50 GMT

for A2+A12+A22+A32+A42+.............as far as you like, try:

=SUMPRODUCT((A1:A65535)*(MOD(ROW(A1:A65535)+8,10)=0))

Signature

Gary''s Student - gsnu200745

> Hello,
>
[quoted text clipped - 8 lines]
>
> What would this group suggest be the most elegant way in doing so

Reply to this Message

Rate this thread: