Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Functions with OFFSET
Hello,
I am trying to use the following formula as an array (entering using CTRL+SHIFT+ENTER): =(SUM(IF('working sheet'!$B$4:$B$59998=$C14,IF(working sheet'!$J$4:$J $59998="D",OFFSET(working sheet'!B4,0,VLOOKUP(Summary!F12,working sheet'!$EI$3:$EP$19,7,FALSE),65000),0)))) The formula is supposed to be saying. IF In the working sheet column B values = C14 AND In the working sheet column J values = "D" Then SUM The column specifed by the OFFSET vlookup statment. When I use them singularly (i.e the nested IF statements on their own or the OFFSET statement on its own they owrk fine. However when I put them together to try and CTRL+SHIFT+ENTER I get an N/A error. Any thoughts / help would be appreciated. Cheers, Worzell |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Functions with OFFSET
Try (untested by me!):
=(SUM(IF(AND('working sheet'!$B$4:$B$59998=$C14,'working sheet'!$J$4:$J$59998="D"),OFFSET('working sheet'!B4,0,VLOOKUP(Summary!F12,workingsheet!$EI$3 :$EP$19,7,FALSE),65000),0))) " wrote: Hello, I am trying to use the following formula as an array (entering using CTRL+SHIFT+ENTER): =(SUM(IF('working sheet'!$B$4:$B$59998=$C14,IF(working sheet'!$J$4:$J $59998="D",OFFSET(working sheet'!B4,0,VLOOKUP(Summary!F12,working sheet'!$EI$3:$EP$19,7,FALSE),65000),0)))) The formula is supposed to be saying. IF In the working sheet column B values = C14 AND In the working sheet column J values = "D" Then SUM The column specifed by the OFFSET vlookup statment. When I use them singularly (i.e the nested IF statements on their own or the OFFSET statement on its own they owrk fine. However when I put them together to try and CTRL+SHIFT+ENTER I get an N/A error. Any thoughts / help would be appreciated. Cheers, Worzell |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Functions with OFFSET
typos in previous ...
=(SUM(IF(AND('Working sheet'!$B$4:$B$59998=$C14,'Working sheet'!$J$4:$J$59998="D"),OFFSET('Working sheet'!B4,0,VLOOKUP(summary!F12,'Working sheet'!$EI$3:$EP$19,7,FALSE),65000),0))) "Toppers" wrote: Try (untested by me!): =(SUM(IF(AND('working sheet'!$B$4:$B$59998=$C14,'working sheet'!$J$4:$J$59998="D"),OFFSET('working sheet'!B4,0,VLOOKUP(Summary!F12,workingsheet!$EI$3 :$EP$19,7,FALSE),65000),0))) " wrote: Hello, I am trying to use the following formula as an array (entering using CTRL+SHIFT+ENTER): =(SUM(IF('working sheet'!$B$4:$B$59998=$C14,IF(working sheet'!$J$4:$J $59998="D",OFFSET(working sheet'!B4,0,VLOOKUP(Summary!F12,working sheet'!$EI$3:$EP$19,7,FALSE),65000),0)))) The formula is supposed to be saying. IF In the working sheet column B values = C14 AND In the working sheet column J values = "D" Then SUM The column specifed by the OFFSET vlookup statment. When I use them singularly (i.e the nested IF statements on their own or the OFFSET statement on its own they owrk fine. However when I put them together to try and CTRL+SHIFT+ENTER I get an N/A error. Any thoughts / help would be appreciated. Cheers, Worzell |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Functions with OFFSET
Apologies just sorted it.
For info the problems appears to have been at the end of the whole formula... =(SUM(IF('Key Delivery working sheet'!$B$4:$B$59998=$C14,IF('Key Delivery working sheet'!$J$4:$J$59998="D",OFFSET('Key Delivery working sheet'!$B$4,0,VLOOKUP(Summary!$F$12,'Key Delivery working sheet'!$EI $3:$EP$19,7,FALSE),65000),0)))) It was the 65000 that was causing the problem as it was looking at a disproportionate number of cells... When changed to: =(SUM(IF('Key Delivery working sheet'!$B$4:$B$59998=$C14,IF('Key Delivery working sheet'!$J$4:$J$59998="D",OFFSET('Key Delivery working sheet'!$B$4,0,VLOOKUP(Summary!$F$12,'Key Delivery working sheet'!$EI $3:$EP$19,7,FALSE),59994),0)))) i.e 65000 to 59994 the number between B4 & B59998 it is now working like a dream. Many thanks all the same!!! Worzell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
offset() with row() and col() functions? | Excel Worksheet Functions | |||
arguments in MATCH and OFFSET functions | Excel Worksheet Functions | |||
Trying to use sumif and offset functions | Excel Discussion (Misc queries) | |||
AVERAGE / OFFSET FUNCTIONS, TODAY.. | Excel Discussion (Misc queries) | |||
Offset and Indirect functions | Excel Discussion (Misc queries) |