software

I have in column quot;Tquot; 1800 rows with numbers , there are blank cells separating
the rows in different sequences. I need to find the sum of each sequence ,
from blank cell to blank cell . there are aprox. 70 sequences.
The blank cells have formulas in them, I have a formula but only works if
the blank cells are empty (no formulas in them) I can not change the blank
cells to empty cells as I need the formulas .

The formula I have is :
{=IF(AND((ISBLANK(T2),ISNUMBER(T3)),SUM(OFFSET(T3, 0,0,MATCH(TRUE,ISBLANK(T3:T26),0)-1)))}can some one help please

regards bill gras
--
bill gras

Try

=IF(AND(T2=quot;quot;,ISNUMBER(T3)),SUM(OFFSET(T3,0,0,MATC H(TRUE,T3:T26=quot;quot;,0)-1)),quot;quot;
)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;bill grasquot; gt; wrote in message
...
gt; I have in column quot;Tquot; 1800 rows with numbers , there are blank cells
separating
gt; the rows in different sequences. I need to find the sum of each sequence ,
gt; from blank cell to blank cell . there are aprox. 70 sequences.
gt; The blank cells have formulas in them, I have a formula but only works if
gt; the blank cells are empty (no formulas in them) I can not change the blank
gt; cells to empty cells as I need the formulas .
gt;
gt; The formula I have is :
gt;
{=IF(AND((ISBLANK(T2),ISNUMBER(T3)),SUM(OFFSET(T3, 0,0,MATCH(TRUE,ISBLANK(T3:
T26),0)-1)))}
gt;
gt;
gt; can some one help please
gt;
gt; regards bill gras
gt; --
gt; bill gras
quot;bill grasquot; gt; skrev i en meddelelse
...
gt;I have in column quot;Tquot; 1800 rows with numbers , there are blank cells
gt;separating
gt; the rows in different sequences. I need to find the sum of each sequence ,
gt; from blank cell to blank cell . there are aprox. 70 sequences.
gt; The blank cells have formulas in them, I have a formula but only works if
gt; the blank cells are empty (no formulas in them) I can not change the blank
gt; cells to empty cells as I need the formulas .
gt;
gt; The formula I have is :
gt; {=IF(AND((ISBLANK(T2),ISNUMBER(T3)),SUM(OFFSET(T3, 0,0,MATCH(TRUE,ISBLANK(T3:T26),0)-1)))}
gt;
gt;
gt; can some one help please
gt;
gt; regards bill gras
gt; --
gt; bill gras

Hi Bill

I take it, that column T contains the same formula in all cells,
sometimes generating a number sometimes generating a blank quot;quot;
(or is it a space quot; quot;?)

Under these conditions (quot;quot;) this array formula will do the trick:

Enter in e.g. G2 as one line (G1 must be present and empty or contain text,
i.e. the formula cannot be entered in a cell in row 1):

=SUM(OFFSET($T$2,,,INDEX(SMALL(IF($T$2:$T$2000=quot;quot;, ROW($T$2:$T$2000)-
ROW($T$2) 1),ROW(INDIRECT(quot;1:quot;amp;COUNTIF($T$2:$T$200 0,quot;quot;)))),
ROW()-ROW($T$2) 1,1)))-SUM($G$1:G1)

Enter the formula with lt;Shiftgt;lt;Ctrlgt;lt;Entergt;, also if you edit it later.

Please notice, that if you enter the formula in e.g. U2, SUM($G$1:G1)
must be changed to SUM($U$1:U1)

Copy G2 down with the fill handle (the little square in the lower right
corner of the cell).

In order to find the sum of the last sequence in column T, the last
cell in column T must generate a quot;quot;

Tested in Excel 2000.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Hi Bob
Thank you very much as always spot on

regards bill gras
--
bill grasquot;Bob Phillipsquot; wrote:

gt; Try
gt;
gt; =IF(AND(T2=quot;quot;,ISNUMBER(T3)),SUM(OFFSET(T3,0,0,MATC H(TRUE,T3:T26=quot;quot;,0)-1)),quot;quot;
gt; )
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;bill grasquot; gt; wrote in message
gt; ...
gt; gt; I have in column quot;Tquot; 1800 rows with numbers , there are blank cells
gt; separating
gt; gt; the rows in different sequences. I need to find the sum of each sequence ,
gt; gt; from blank cell to blank cell . there are aprox. 70 sequences.
gt; gt; The blank cells have formulas in them, I have a formula but only works if
gt; gt; the blank cells are empty (no formulas in them) I can not change the blank
gt; gt; cells to empty cells as I need the formulas .
gt; gt;
gt; gt; The formula I have is :
gt; gt;
gt; {=IF(AND((ISBLANK(T2),ISNUMBER(T3)),SUM(OFFSET(T3, 0,0,MATCH(TRUE,ISBLANK(T3:
gt; T26),0)-1)))}
gt; gt;
gt; gt;
gt; gt; can some one help please
gt; gt;
gt; gt; regards bill gras
gt; gt; --
gt; gt; bill gras
gt;
gt;
gt;