Nested IF Functions with OFFSET

[worzell]

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

 

[Guest]

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)))

 

[Guest]

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)))

 

[worzell]

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