Sum(Offset(Cell(address - not working

[carrach]

Excel 2007 - I need to - find first non blank cell in row and then sum that and next 11 cells. Some of these 12 cells may be blank. I have been using this formula which is not working:

Please Login or Register  to view this content.

in this particular row the first populated cell is AM7 which is returned correctly in the CELL part of the formula above but obviously I have not put it together correctly. Also it may be necessary to consider rows where the first value is within the last 12 columns and therefore there may be less than 12 columns to sum. The table contains 1270 rows starting at row 7 and columns B to BI inclusive. Please could you tell me where I have gone wrong. Thanks in advance

[XOR LX]

Hi.

So do you mean that there are entries in columns beyond column BI which would potentially interfere with any solution which continued to sum beyond that column?

If not, you could use either of the following array formulas**:

=SUM(OFFSET(INDEX(B7:BI7,MATCH(TRUE,B7:BI7<>"",0)),,,,12))

=SUM(INDEX(B7:XFD7,N(IF(1,MATCH(TRUE,B7:BI7<>"",0)+{0,1,2,3,4,5,6,7,8,9,10,11}))))

I tend to prefer the latter since it is non-volatile.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).