Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Not showing blank and non blank items in filter mode for values | Excel Worksheet Functions | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
conditional formatting:highlight row based on blank or non-blank c | Excel Discussion (Misc queries) |