Hi, I had the formula:

=SUM(N(OFFSET($A$9,0,SMALL(IF((L9:BH90)*(MOD(COLU MN(L9:BH9)-12,8)=0),COLUMN(L9:BH9),COLUMN(IV9)),{1,2,3,4,5})-1,1,1)))

I had to ad in extra columns for my nightshift

I tried to adjust the formula as follows:

=SUM(N(OFFSET($A$14,0,SMALL(IF((K14:BM140)*(MOD(C OLUMN(K14:BM14)-12,8)=0),COLUMN(K14:BM14),COLUMN(IV14)),{1,2,3,4,5 })-1,1,1)))

but the outcome is not the same,

what do I have to do to do any adjustments in future, should I wish t
ad more columns in between.

My cells a
K14, T14, AC14, AL14, AU14, BD14, BM14 (Every 9th cell)

help will be most appreciated

--
Message posted from http://www.ExcelForum.com

Change the eight to a nine.

sonar < wrote:
Hi, I had the formula:

=SUM(N(OFFSET($A$9,0,SMALL(IF((L9:BH90)*(MOD(COLU MN(L9:BH9)-12,8)=0),COLUMN(L9:BH9),COLUMN(IV9)),{1,2,3,4,5})-1,1,1)))

I had to ad in extra columns for my nightshift

I tried to adjust the formula as follows:

=SUM(N(OFFSET($A$14,0,SMALL(IF((K14:BM140)*(MOD(C OLUMN(K14:BM14)-12,8)=0),COLUMN(K14:BM14),COLUMN(IV14)),{1,2,3,4,5 })-1,1,1)))

but the outcome is not the same,

what do I have to do to do any adjustments in future, should I wish to
ad more columns in between.

My cells a
K14, T14, AC14, AL14, AU14, BD14, BM14 (Every 9th cell)

help will be most appreciated.


---
Message posted from http://www.ExcelForum.com/

The formula, if I am not wrong, seems to see my BE14 (overtime) cell
and not my BD (normal time) cell, any reason?

What does the -12 and the 9 mean

--
Message posted from http://www.ExcelForum.com

Well, I'm not really sure what you are trying to do with this formula,
but it seems that the 12 should be changed to 11 since you added a
column to the left (K=column 11) instead of starting at L=column12. The
9 looks at every ninth column.

sonar < wrote:

The formula, if I am not wrong, seems to see my BE14 (overtime) cell,
and not my BD (normal time) cell, any reason?

What does the -12 and the 9 mean?


---
Message posted from http://www.ExcelForum.com/