SUMPRODUCT multiple criteria on named ranges

[ron2k]

Hi guys,

Say, I have three named ranges (no header rows):
bze2011 = A1:N1250
czl2011 = A1260:N2569
bmp2011 = A2580:N4976

Column A contain, say, product codes - T521,T523,T568
Column N contain total sales for that product.

On say another worksheet:
A1=T521
A2=T523
A3=T568

On B1, I'd like the sumproduct of A1:A3, on Column N of named range bze2011 where products listed on A1:A3 are found on column A of that range (A1:A1250).

I don't want to use:

=IF(ISNA(VLOOKUP(A1,bze2011,13,FALSE)),0,VLOOKUP(A1,bze2011,13,FALSE))+IF(ISNA(VLOOKUP(A2,bze2011,13,FALSE)),0,VLOOKUP(A2,bze2011,13,FALSE))+IF(ISNA(VLOOKUP(A3,bze2011,13,FALSE)),0,VLOOKUP(A3,bze2011,13,FALSE))

Is there a way?

EDIT: Note that I'm gonna put the sumproduct formula on another workbook, so offset and such may not work.

Thanks

 

[ron2k]

Hmn,

I've given this a try, but it's working guys I get #VALUE! error.

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A3,INDEX('TB Branches - Yearly.xls'!bze2011,,1),0)),INDEX('TB Branches - Yearly.xls'!bze2011,,14))

I guess, I'll have to start consider the vlookup formula.

 

[Marcelo Branco]

Hi,

Maybe this

As bze2011 is a cell-address in Excel 2010, i changed the name to MyRange

=SUMPRODUCT(--(((MyRange=A1)+(MyRange=A2)+(MyRange=A3))>0),OFFSET(MyRange,0,13))

HTH

M.

 

[ron2k]

I haven't tried this, but not quite sure I want to try it. Thanks though, the reason for the hesitation is because OFFSET doesn't work on closed workbooks.

Any other suggestion?

Ron

EDIT: I tried it and it gives a #NAME? error

 

[Marcelo Branco]

Ron,

I didnt know you are working with 2 different workbooks.

In your OP you wrote
"On say another worksheet:
A1=T521
A2=T523
A3=T568"

I have to rethink

You got an error #NAME... Excel 2003?

M.

 

[ron2k]

Both workbooks are saved as 2003; but I work on xl07.

Other types of arrays may work, but I'm not that versed in that type of formulas.

Thanks,

 

[Haseeb Avarakkan]

Hello, Try

=SUMPRODUCT(--ISNUMBER(MATCH(INDEX(bze2011,,1),A1:A3,0)),INDEX(bze2011,,14))

EDIT: If your workbook is on XL07 bze2011 is a valid cell reference.

 

[ron2k]

Hey that made it, thank you Haseeb, that almost looks like the shot I took when I wrote the formula on post #2 above.

Thanks a mil,

Ron