Selecting evenly spaced cells

C

Chris

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
 
C

Chris

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"
 
S

Sandy Mann

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.

--
HTH

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
C

Chris

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
 
G

Gary''s Student

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

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

Sandy Mann

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.

--
Regards,

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
P

Peo Sjoblom

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"



--


Regards,


Peo Sjoblom
 
R

Ron Coderre

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)
 
Top