Selecting evenly spaced cells

  • Thread starter Thread starter Chris
  • Start date Start date
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
 
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"
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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
 
for A2+A12+A22+A32+A42+.............as far as you like, try:

=SUMPRODUCT((A1:A65535)*(MOD(ROW(A1:A65535)+8,10)=0))
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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
 
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)
 
Back
Top