problem skipping columns

[TheFox]

I've got a question that probably has been asked before, but here goe
anyway. I want to select a range of numbers in various columns and ad
them together so I would put =sum(d47:bn47) The problem is that I wan
to skip every other column. So I would want to skip e47 g47 i47 and s
forth. How would I be able to do this without selecting each cel
individually

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(MOD(COLUMN(D47:B47),2)=0),D47:B47)

 

[TheFox]

Thank you very much. I was very close to this I just was off by a bit
Thank you for filling in all the blanks :

 

[RichardO]

Hi Frank,

Just curious about the formula below: What does the "2" after B4
stand for? What does the "0" after the "2" stand for?

Thanks a lot.


RichardO

 

[Frank Kabel]

Hi
the MOD function return the rest of a division. e.g.
=MOD(4,2)
divides 4 with 2 and returns the rest -> that is zero
or
=MOD(8,3)
will return the rest from 8/3 = 2

 

[AlfD]

RichardO

Or to put it another way

the 2 after the B47 is the "every 2 rows" bit. The 0 after that select
the cases where the column number divides exactly by 2
so there is 0 remainder.

If he'd wanted the odd numbered columns, he'd need 1 instead of th
zero.

Alf

P.S. Did anyone ever see Charlie Chaplin in City Lights