sum problem
 

I have in column "T" 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

sum problem
 

Try

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bill gras" wrote in message
...
I have in column "T" 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

sum problem
 

"bill gras" skrev i en meddelelse
...
I have in column "T" 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

Hi Bill

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

Under these conditions ("") 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="", ROW($T$2:$T$2000)-
ROW($T$2)+1),ROW(INDIRECT("1:"&COUNTIF($T$2:$T$200 0,"")))),
ROW()-ROW($T$2)+1,1)))-SUM($G$1:G1)

Enter the formula with <Shift<Ctrl<Enter, 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 ""

Tested in Excel 2000.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

sum problem
 

Hi Bob
Thank you very much as always spot on

regards bill gras
--
bill gras


"Bob Phillips" wrote:

Try

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bill gras" wrote in message
...
I have in column "T" 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