Function(s) that return multiple separated references
 

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

Function(s) that return multiple separated references
 

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

Function(s) that return multiple separated references
 

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