software

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: www.excelforum.com/member.php...oamp;userid=32513
View this thread: www.excelforum.com/showthread...hreadid=523003Hi 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 Govierquot;akullenquot; gt; wrote
in message ...
gt;
gt; Hello,
gt;
gt; I am trying to achieve a kind of combination of OFFSET and COUNT i.e.
gt;
gt; My problem is that OFFSET requires continuously filled cells which I
gt; do
gt; not have. I could have, as an example, a number in every 5th row in a
gt; column. Do you have any proposals on how to come around this?
gt;
gt; Thanks in advance
gt; Anders
gt;
gt;
gt; --
gt; akullen
gt; ------------------------------------------------------------------------
gt; akullen's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32513
gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=523003
gt;

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:
www.excelforum.com/showthread.php?t=512386

Thanks
Anders.--
akullen
------------------------------------------------------------------------
akullen's Profile: www.excelforum.com/member.php...oamp;userid=32513
View this thread: www.excelforum.com/showthread...hreadid=523003