Function(s) that return multiple separated references

A

akullen

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 d
not have. I could have, as an example, a number in every 5th row in
column. Do you have any proposals on how to come around this?

Thanks in advance
Ander
 
R

Roger Govier

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
 
Top