Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have some problem with database | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |