ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Adding every sixth value (https://www.excelbanter.com/excel-discussion-misc-queries/174113-adding-every-sixth-value.html)

KLock
Adding every sixth value
 
I have to add every sixth value in a column summed over 52 value. Is there
an equation to do this so I don't have to input each cell.

Example

Column 1 2 3 4 5 1 2 3 4 5

So the equation I need is Column (1+1), Column (2+2), so on

RagDyeR
Adding every sixth value
 
If you went from Column A to Column IV, *only* the "first column series"
would contain 52 values.
All the rest would have 51.

That being the case, this formula will total column A to IV on Row10,
starting in A:

=SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10))

Starting in B:
=SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"KLock" wrote in message
...
I have to add every sixth value in a column summed over 52 value. Is there
an equation to do this so I don't have to input each cell.

Example

Column 1 2 3 4 5 1 2 3 4 5

So the equation I need is Column (1+1), Column (2+2), so on



KLock
Adding every sixth value
 
Ok that probably will work the only problems are

I need to start with column B, since column A is the header, and the
equation I am inputting is at the end of the row, so I don't want to add
these cells.


"RagDyeR" wrote:

If you went from Column A to Column IV, *only* the "first column series"
would contain 52 values.
All the rest would have 51.

That being the case, this formula will total column A to IV on Row10,
starting in A:

=SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10))

Starting in B:
=SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"KLock" wrote in message
...
I have to add every sixth value in a column summed over 52 value. Is there
an equation to do this so I don't have to input each cell.

Example

Column 1 2 3 4 5 1 2 3 4 5

So the equation I need is Column (1+1), Column (2+2), so on




KLock
Adding every sixth value
 
Since Column A has the headers and I inputting the equation in IH9

I changed the equation to
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9))
but the calculations are wrong is there anything else I need to change for
this adjustment or is this just the wrong equation for this situation

"KLock" wrote:

Ok that probably will work the only problems are

I need to start with column B, since column A is the header, and the
equation I am inputting is at the end of the row, so I don't want to add
these cells.


"RagDyeR" wrote:

If you went from Column A to Column IV, *only* the "first column series"
would contain 52 values.
All the rest would have 51.

That being the case, this formula will total column A to IV on Row10,
starting in A:

=SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10))

Starting in B:
=SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"KLock" wrote in message
...
I have to add every sixth value in a column summed over 52 value. Is there
an equation to do this so I don't have to input each cell.

Example

Column 1 2 3 4 5 1 2 3 4 5

So the equation I need is Column (1+1), Column (2+2), so on




RagDyeR
Adding every sixth value
 
Starting in B:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+3,5)=0)*(B9:IG9))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+2,5)=0)*(B9:IG9))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+1,5)=0)*(B9:IG9))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(B9:IG9),5)=0)*(B9:IG9))

Starting in F:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)-1,5)=0)*(B9:IG9))


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KLock" wrote in message
...
Since Column A has the headers and I inputting the equation in IH9

I changed the equation to
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9))
but the calculations are wrong is there anything else I need to change for
this adjustment or is this just the wrong equation for this situation

"KLock" wrote:

Ok that probably will work the only problems are

I need to start with column B, since column A is the header, and the
equation I am inputting is at the end of the row, so I don't want to add
these cells.


"RagDyeR" wrote:

If you went from Column A to Column IV, *only* the "first column
series"
would contain 52 values.
All the rest would have 51.

That being the case, this formula will total column A to IV on Row10,
starting in A:

=SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10))

Starting in B:
=SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"KLock" wrote in message
...
I have to add every sixth value in a column summed over 52 value. Is
there
an equation to do this so I don't have to input each cell.

Example

Column 1 2 3 4 5 1 2 3 4 5

So the equation I need is Column (1+1), Column (2+2), so on

KLock
Adding every sixth value
 
Ok that works thanks for the help

"RagDyer" wrote:

Starting in B:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+3,5)=0)*(B9:IG9))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+2,5)=0)*(B9:IG9))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+1,5)=0)*(B9:IG9))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(B9:IG9),5)=0)*(B9:IG9))

Starting in F:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)-1,5)=0)*(B9:IG9))


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KLock" wrote in message
...
Since Column A has the headers and I inputting the equation in IH9

I changed the equation to
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9))
but the calculations are wrong is there anything else I need to change for
this adjustment or is this just the wrong equation for this situation

"KLock" wrote:

Ok that probably will work the only problems are

I need to start with column B, since column A is the header, and the
equation I am inputting is at the end of the row, so I don't want to add
these cells.


"RagDyeR" wrote:

If you went from Column A to Column IV, *only* the "first column
series"
would contain 52 values.
All the rest would have 51.

That being the case, this formula will total column A to IV on Row10,
starting in A:

=SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10))

Starting in B:
=SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"KLock" wrote in message
...
I have to add every sixth value in a column summed over 52 value. Is
there
an equation to do this so I don't have to input each cell.

Example

Column 1 2 3 4 5 1 2 3 4 5

So the equation I need is Column (1+1), Column (2+2), so on

David Biddulph[_2_]
Adding every sixth value
 
If you want every 6th value, I would have thought that you'd need MOD(...,6)
and not MOD(...,5), but the OP's example looked as if he might want every
5th though he said every 6th?
--
David Biddulph

"RagDyer" wrote in message
...
Starting in B:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+3,5)=0)*(B9:IG9))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+2,5)=0)*(B9:IG9))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+1,5)=0)*(B9:IG9))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(B9:IG9),5)=0)*(B9:IG9))

Starting in F:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)-1,5)=0)*(B9:IG9))


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KLock" wrote in message
...
Since Column A has the headers and I inputting the equation in IH9

I changed the equation to
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9))
but the calculations are wrong is there anything else I need to change
for
this adjustment or is this just the wrong equation for this situation

"KLock" wrote:

Ok that probably will work the only problems are

I need to start with column B, since column A is the header, and the
equation I am inputting is at the end of the row, so I don't want to add
these cells.


"RagDyeR" wrote:

If you went from Column A to Column IV, *only* the "first column
series"
would contain 52 values.
All the rest would have 51.

That being the case, this formula will total column A to IV on Row10,
starting in A:

=SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10))

Starting in B:
=SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"KLock" wrote in message
...
I have to add every sixth value in a column summed over 52 value. Is
there
an equation to do this so I don't have to input each cell.

Example

Column 1 2 3 4 5 1 2 3 4 5

So the equation I need is Column (1+1), Column (2+2), so on

RagDyeR
Adding every sixth value
 
You're welcome, and thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KLock" wrote in message
...
Ok that works thanks for the help

"RagDyer" wrote:

Starting in B:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+3,5)=0)*(B9:IG9))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+2,5)=0)*(B9:IG9))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+1,5)=0)*(B9:IG9))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(B9:IG9),5)=0)*(B9:IG9))

Starting in F:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)-1,5)=0)*(B9:IG9))


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"KLock" wrote in message
...
Since Column A has the headers and I inputting the equation in IH9

I changed the equation to
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9))
but the calculations are wrong is there anything else I need to change
for
this adjustment or is this just the wrong equation for this situation

"KLock" wrote:

Ok that probably will work the only problems are

I need to start with column B, since column A is the header, and the
equation I am inputting is at the end of the row, so I don't want to
add
these cells.


"RagDyeR" wrote:

If you went from Column A to Column IV, *only* the "first column
series"
would contain 52 values.
All the rest would have 51.

That being the case, this formula will total column A to IV on
Row10,
starting in A:

=SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10))

Starting in B:
=SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"KLock" wrote in message
...
I have to add every sixth value in a column summed over 52 value.
Is
there
an equation to do this so I don't have to input each cell.

Example

Column 1 2 3 4 5 1 2 3 4 5

So the equation I need is Column (1+1), Column (2+2), so on

RagDyeR
Adding every sixth value
 
It's usually a good idea to give examples preference over text.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
If you want every 6th value, I would have thought that you'd need
MOD(...,6)
and not MOD(...,5), but the OP's example looked as if he might want every
5th though he said every 6th?
--
David Biddulph

"RagDyer" wrote in message
...
Starting in B:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+3,5)=0)*(B9:IG9))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+2,5)=0)*(B9:IG9))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+1,5)=0)*(B9:IG9))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(B9:IG9),5)=0)*(B9:IG9))

Starting in F:
=SUMPRODUCT((MOD(COLUMN(B9:IG9)-1,5)=0)*(B9:IG9))


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"KLock" wrote in message
...
Since Column A has the headers and I inputting the equation in IH9

I changed the equation to
=SUMPRODUCT((MOD(COLUMN(B9:IG9)+4,5)=0)*(B9:IG9))
but the calculations are wrong is there anything else I need to change
for
this adjustment or is this just the wrong equation for this situation

"KLock" wrote:

Ok that probably will work the only problems are

I need to start with column B, since column A is the header, and the
equation I am inputting is at the end of the row, so I don't want to
add
these cells.


"RagDyeR" wrote:

If you went from Column A to Column IV, *only* the "first column
series"
would contain 52 values.
All the rest would have 51.

That being the case, this formula will total column A to IV on
Row10,
starting in A:

=SUMPRODUCT((MOD(COLUMN(10:10)+4,5)=0)*(10:10))

Starting in B:
=SUMPRODUCT((MOD(COLUMN(10:10)+3,5)=0)*(10:10))

Starting in C:
=SUMPRODUCT((MOD(COLUMN(10:10)+2,5)=0)*(10:10))

Starting in D:
=SUMPRODUCT((MOD(COLUMN(10:10)+1,5)=0)*(10:10))

Starting in E:
=SUMPRODUCT((MOD(COLUMN(10:10),5)=0)*(10:10))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"KLock" wrote in message
...
I have to add every sixth value in a column summed over 52 value.
Is
there
an equation to do this so I don't have to input each cell.

Example

Column 1 2 3 4 5 1 2 3 4 5

So the equation I need is Column (1+1), Column (2+2), so on


All times are GMT +1. The time now is 02:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com