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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 04:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com