Add cell in every 7th column

[Igorin]

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?

 

[Mike H]

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

 

[David Biddulph]

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

 

[Igorin]

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?

 

[T. Valko]

Try this:

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

Adjust the end of range as needed

 

[Igorin]

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.

 

[Igorin]

It works!!!

Thanks a lot, T.!

--
igor

Try this:

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

Adjust the end of range as needed

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[David Biddulph]

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]

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.