Nested IF with VLOOKUP #2

[alisapooh]

This is what I have:

=SUMIF(Profit!D:D,VLOOKUP(Profit!D2,SlotCodes!A:C,3)={1,3},Profit!E:E)

And it doesn't work! What is wrong?

This is what I want it to do:

Worksheets involved: Forecast, Profit, SlotCodes

On Forecast, lookup the code (either 2 letters or 2 numbers) in column D on Profit. Match that code in column A on SlotCodes. Lookup answer is in column C (possible choices, 1,2,3,4).

Take that answer, and if it is a 1 or 3, then sum the cooresponding column E on Profit.

So, the sum of column E on Profit that coorespondes to a 1 or 3 needs to go in cell B2 on the Forecast worksheet.

Does that make sense?

Thanks.

Alisa

[daddylonglegs]

Hello Alisa,

Try using an array formula like this

=SUM(IF(ISNUMBER(MATCH(Profit!D2:D100,IF((SlotCodes!C2:C10=1)+(SlotCodes!C2:C10=3),SlotCodes!A2:A10,""),0)),E2:E100))

confirmed with CTRL+SHIFT+ENTER

extend ranges as necessary but you can't use whole columns

[alisapooh]

I got a zero for an answer using that formula

[daddylonglegs]

Oh! I missed the sheet name of the last range so it should be

=SUM(IF(ISNUMBER(MATCH(Profit!D2:D100,IF((SlotCodes!C2:C10=1)+(SlotCodes!C2:C10= 3),SlotCodes!A2:A10,""),0)),Profit!E2:E100))

Make sure you use CTRL+SHIFT+ENTER so that curly braces appaer around the formula.....

[DonkeyOte]

Another alternative perhaps would be to store the SlotCode result in a spare column on Profit sheet. Assume for sake of demo Col F on Profit sheet is spare, then:

Please Login or Register  to view this content.

Then your Forecast formula becomes

Please Login or Register  to view this content.