indexing - Sum(Offset(Cell(address - not working -

excel 2007 - need - find first non blank cell in row , sum , next 11 cells. of these 12 cells may blank. have been using formula not working:

=sum(offset(cell("address",index(b7:bi7,match(false,isblank(b7:bi7),0))),0,12)) 

in particular row first populated cell am7 returned correctly in cell part of formula above have not put correctly. may necessary consider rows first value within last 12 columns , therefore there may less 12 columns sum. table contains 1270 rows starting @ row 7 , columns b bi inclusive. please tell me have gone wrong.

a cell range can constructed pair of index functions linked colon same conventional, hard-coded cell ranges.

in a7 standard formula,

=sum(index(b7:bi7, aggregate(15, 6, column(a:bh)/(b7:bi7<>""), 1)):      index(b7:bi7, min(aggregate(15, 6, column(a:bh)/(b7:bi7<>""), 1)+10, columns(b:bi)))) 

the aggregate¹ function used provide position within columns b:bi start. min function ensures adding 10 start position (for end position) never exceed number of columns in b:bi.

for pre-xl2010 installations, aggregate function's small sub-function can reproduced array formula².

in a7 array formula²,

=sum(index(b7:bi7, small(if(b7:bi7<>"", column(a:bh)), 1)):      index(b7:bi7, min(small(if(b7:bi7<>"", column(a:bh)), 1)+10, 60))) 

 fill down necessary.

   
   

---

¹ _{the aggregate function introduced excel 2010. not available in earlier versions.}

² _{array formulas need finalized ctrl+shift+enter↵. once entered first cell correctly, can filled or copied down or right other formula. try , reduce full-column references ranges more closely representing extents of actual data. array formulas chew calculation cycles logarithmically practise narrow referenced ranges minimum. see guidelines , examples of array formulas more information.}