sum until next blank
 

I want to create an if statement in excel that fits into the following

=if(H10="",sum(H11:*****Here*****),IF(G11="","",G1 1*IF(ISNA(VLOOKUP(F11,A:H,8,FALSE))=TRUE,VLOOKUP(F 11,PRICING!A:C,3,FALSE),VLOOKUP(F11,A:H,8,FALSE))) )

where here is the next cell with a value of ""

which would effectively do the following and be a completely coded column
without any need for change


SUM
number
number
number
number

SUM
Number
Number
Number

SUM
Number
Number
Number
Number
Number
Number

sum until next blank
 

=IF(H10="",SUM(H11:INDEX(H:H,MIN(IF(H11:H1000="",R OW(H11:H1000))))),IF(G11="","",G11*IF(ISNA(VLOOKUP (F11,A:H,8,FALSE))=TRUE,VLOOKUP(F11,PRICING!A:C,3, FALSE),VLOOKUP(F11,A:H,8,FALSE))))

this is now an array formula, so commit with Ctrl-Shift-Enetr, not just
Enter.

--
__________________________________
HTH

Bob

"woozlemonk" wrote in message
...
I want to create an if statement in excel that fits into the following

=if(H10="",sum(H11:*****Here*****),IF(G11="","",G1 1*IF(ISNA(VLOOKUP(F11,A:H,8,FALSE))=TRUE,VLOOKUP(F 11,PRICING!A:C,3,FALSE),VLOOKUP(F11,A:H,8,FALSE))) )

where here is the next cell with a value of ""

which would effectively do the following and be a completely coded column
without any need for change


SUM
number
number
number
number

SUM
Number
Number
Number

SUM
Number
Number
Number
Number
Number
Number

sum until next blank
 

it returned a #value

I did however notice an error I made in typing it out into here..
it should have been sum (H12:***here***)

to clarify
I need to be able to post it into every cell in the column but the top two
but to be able to copy and paste into the column the code in the future

I'm already using most of that code
example:

""
=SUM(H4:H9)
=IF(G4="","",G4*IF(ISNA(VLOOKUP(F4,A:H,8,FALSE))=T RUE,VLOOKUP(F4,PRICING!A:C,3,FALSE),VLOOKUP(F4,A:H ,8,FALSE)))
=IF(G5="","",G5*IF(ISNA(VLOOKUP(F5,A:H,8,FALSE))=T RUE,VLOOKUP(F5,PRICING!A:C,3,FALSE),VLOOKUP(F5,A:H ,8,FALSE)))
=IF(G6="","",G6*IF(ISNA(VLOOKUP(F6,A:H,8,FALSE))=T RUE,VLOOKUP(F6,PRICING!A:C,3,FALSE),VLOOKUP(F6,A:H ,8,FALSE)))
=IF(G7="","",G7*IF(ISNA(VLOOKUP(F7,A:H,8,FALSE))=T RUE,VLOOKUP(F7,PRICING!A:C,3,FALSE),VLOOKUP(F7,A:H ,8,FALSE)))
=IF(G8="","",G8*IF(ISNA(VLOOKUP(F8,A:H,8,FALSE))=T RUE,VLOOKUP(F8,PRICING!A:C,3,FALSE),VLOOKUP(F8,A:H ,8,FALSE)))
""

and then again in a similar fashion but with differant numbers of items in
the string

I wish to integrate the sum code into the rest of it and make it check for
the blanks to define the area so that I have a single code for the entire
column copied and pasted regardless of the rest of the worksheet

I should clarify I'm using 2003

thanks
Dave


"Bob Phillips" wrote:

=IF(H10="",SUM(H11:INDEX(H:H,MIN(IF(H11:H1000="",R OW(H11:H1000))))),IF(G11="","",G11*IF(ISNA(VLOOKUP (F11,A:H,8,FALSE))=TRUE,VLOOKUP(F11,PRICING!A:C,3, FALSE),VLOOKUP(F11,A:H,8,FALSE))))

this is now an array formula, so commit with Ctrl-Shift-Enetr, not just
Enter.

--
__________________________________
HTH

Bob

"woozlemonk" wrote in message
...
I want to create an if statement in excel that fits into the following

=if(H10="",sum(H11:*****Here*****),IF(G11="","",G1 1*IF(ISNA(VLOOKUP(F11,A:H,8,FALSE))=TRUE,VLOOKUP(F 11,PRICING!A:C,3,FALSE),VLOOKUP(F11,A:H,8,FALSE))) )

where here is the next cell with a value of ""

which would effectively do the following and be a completely coded column
without any need for change


SUM
number
number
number
number

SUM
Number
Number
Number

SUM
Number
Number
Number
Number
Number
Number