Hello,

I am trying to achieve a kind of combination of OFFSET and COUNT i.e.

My problem is that OFFSET requires continuously filled cells which I do
not have. I could have, as an example, a number in every 5th row in a
column. Do you have any proposals on how to come around this?

Thanks in advance
Anders


--
akullen
------------------------------------------------------------------------
akullen's Profile: http://www.excelforum.com/member.php...o&userid=32513
View this thread: http://www.excelforum.com/showthread...hreadid=523003

Hi Anders

One way
=SUMPRODUCT(--(MOD(ROW(A1:A100),5)=0),A1:A100)

This would sum values in cells A5, A10, A15 etc.
The MOD( ( ) ,5)=0 part of the formula is testing whether the row number
divides exactly by 5 (in which case the remainder will be 0) and
multiplies the result True or False by the values in the cells in that
column. The double unary minus (--) coerces these True's to 1's and
False's to 0's to enable the calculation to be made.

Making it MOD( ( ),3)=0 would make it every third row

--
Regards

Roger Govier


"akullen" wrote
in message ...

Hello,

I am trying to achieve a kind of combination of OFFSET and COUNT i.e.

My problem is that OFFSET requires continuously filled cells which I
do
not have. I could have, as an example, a number in every 5th row in a
column. Do you have any proposals on how to come around this?

Thanks in advance
Anders


--
akullen
------------------------------------------------------------------------
akullen's Profile:
http://www.excelforum.com/member.php...o&userid=32513
View this thread:
http://www.excelforum.com/showthread...hreadid=523003

Hi Roger

Thanks for the answer. My example was not very good. It is not always
the same number of rows in between.
Anyway, I found a way to solve my problem here on the forum. Check this
great thread out:
http://www.excelforum.com/showthread.php?t=512386

Thanks
Anders.


--
akullen
------------------------------------------------------------------------
akullen's Profile: http://www.excelforum.com/member.php...o&userid=32513
View this thread: http://www.excelforum.com/showthread...hreadid=523003