Add cell in every 7th column
 

Hello,

How do I write a function that adds the values for the intersection between
row 12 and every 7th column (to give a concrete example) .

To illustrate:

Add the cells G12, N12, U12, AB12, ...

Can somebody give me an idea?

--
igor

Add cell in every 7th column
 

Try this

=SUM(IF(MOD(COLUMN(G12:IV12),7)=0,G12:IV12,0))

This is an array and must be entered with CTRL+Shift+Enter.

Mike

"Igorin" wrote:

Hello,

How do I write a function that adds the values for the intersection between
row 12 and every 7th column (to give a concrete example) .

To illustrate:

Add the cells G12, N12, U12, AB12, ...

Can somebody give me an idea?

--
igor

Add cell in every 7th column
 

=SUMPRODUCT(G12:AB12,--(MOD(COLUMN(G12:AB12),7)=0))
--
David Biddulph

"Igorin" wrote in message
...
Hello,

How do I write a function that adds the values for the intersection
between
row 12 and every 7th column (to give a concrete example) .

To illustrate:

Add the cells G12, N12, U12, AB12, ...

Can somebody give me an idea?

--
igor

Add cell in every 7th column
 

Mike, thank yo very much for the reply,

I just tried this and it returns a null value. By the way, I did not get the
part about using the CTRL+Shift+Enter. I copied the formula and pasted it
inside the cell itself and then modified the cell references.

What did I do wrong?

--
igor


"Mike H" wrote:

Try this

=SUM(IF(MOD(COLUMN(G12:IV12),7)=0,G12:IV12,0))

This is an array and must be entered with CTRL+Shift+Enter.

Mike

"Igorin" wrote:

Hello,

How do I write a function that adds the values for the intersection between
row 12 and every 7th column (to give a concrete example) .

To illustrate:

Add the cells G12, N12, U12, AB12, ...

Can somebody give me an idea?

--
igor

Add cell in every 7th column
 

Try this:

=SUMPRODUCT(--(MOD(COLUMN(G12:BY12)-COLUMN(G12),7)=0),G12:BY12)

Adjust the end of range as needed

--
Biff
Microsoft Excel MVP


"Igorin" wrote in message
...
Hello,

How do I write a function that adds the values for the intersection
between
row 12 and every 7th column (to give a concrete example) .

To illustrate:

Add the cells G12, N12, U12, AB12, ...

Can somebody give me an idea?

--
igor

Add cell in every 7th column
 

Thanks for the help, David,

I'm not getting the right amount. It returns the value in the 7th cell but
not the sum of all th cells.


--
igor


"David Biddulph" wrote:

=SUMPRODUCT(G12:AB12,--(MOD(COLUMN(G12:AB12),7)=0))
--
David Biddulph

"Igorin" wrote in message
...
Hello,

How do I write a function that adds the values for the intersection
between
row 12 and every 7th column (to give a concrete example) .

To illustrate:

Add the cells G12, N12, U12, AB12, ...

Can somebody give me an idea?

--
igor

Add cell in every 7th column
 

It works!!!

Thanks a lot, T.!

--
igor


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(MOD(COLUMN(G12:BY12)-COLUMN(G12),7)=0),G12:BY12)

Adjust the end of range as needed

--
Biff
Microsoft Excel MVP


"Igorin" wrote in message
...
Hello,

How do I write a function that adds the values for the intersection
between
row 12 and every 7th column (to give a concrete example) .

To illustrate:

Add the cells G12, N12, U12, AB12, ...

Can somebody give me an idea?

--
igor

Add cell in every 7th column
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Igorin" wrote in message
...
It works!!!

Thanks a lot, T.!

--
igor


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(MOD(COLUMN(G12:BY12)-COLUMN(G12),7)=0),G12:BY12)

Adjust the end of range as needed

--
Biff
Microsoft Excel MVP


"Igorin" wrote in message
...
Hello,

How do I write a function that adds the values for the intersection
between
row 12 and every 7th column (to give a concrete example) .

To illustrate:

Add the cells G12, N12, U12, AB12, ...

Can somebody give me an idea?

--
igor

Add cell in every 7th column
 

The formula works for me. It gives me the same as =G12+N12+U12+AB12
Perhaps you need to check that you really have numbers in N12, U12, and
AB12, and not text? Make sure that there are no spaces or non-printing
characters. What do =ISNUMBER(N12) and =ISTEXT(N12) give?
--
David Biddulph

"Igorin" wrote in message
...
Thanks for the help, David,

I'm not getting the right amount. It returns the value in the 7th cell but
not the sum of all th cells.


--
igor


"David Biddulph" wrote:

=SUMPRODUCT(G12:AB12,--(MOD(COLUMN(G12:AB12),7)=0))
--
David Biddulph

"Igorin" wrote in message
...
Hello,

How do I write a function that adds the values for the intersection
between
row 12 and every 7th column (to give a concrete example) .

To illustrate:

Add the cells G12, N12, U12, AB12, ...

Can somebody give me an idea?

--
igor

Add cell in every 7th column
 

Note also that my formula is effectively the same as Biff's which you say
works for you. He had:
=SUMPRODUCT(--(MOD(COLUMN(G12:BY12)-COLUMN(G12),7)=0),G12:BY12)

COLUMN(G12) is 7, so that won't affect the MOD result. The only significant
difference is that his range goes on beyond AB12 to BY12, but of course my
formula can be extended to cover whatever range is appropriate. If there
are cells such as N12, U12 and AB12 which you say are being picked up by
Biff';s formula but not by mine, I would be very intrigued to know why.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
The formula works for me. It gives me the same as =G12+N12+U12+AB12
Perhaps you need to check that you really have numbers in N12, U12, and
AB12, and not text? Make sure that there are no spaces or non-printing
characters. What do =ISNUMBER(N12) and =ISTEXT(N12) give?
--
David Biddulph

"Igorin" wrote in message
...
Thanks for the help, David,

I'm not getting the right amount. It returns the value in the 7th cell
but
not the sum of all th cells.


--
igor


"David Biddulph" wrote:

=SUMPRODUCT(G12:AB12,--(MOD(COLUMN(G12:AB12),7)=0))
--
David Biddulph

"Igorin" wrote in message
...
Hello,

How do I write a function that adds the values for the intersection
between
row 12 and every 7th column (to give a concrete example) .

To illustrate:

Add the cells G12, N12, U12, AB12, ...

Can somebody give me an idea?

--
igor