Count multiple rows with vlookup

[Berry]

Dear excellers,

In a workbook called "Order Status 2011'' there are 10.000 rows with
information transfered from access.
Column A is text / column H are quantities:
TEXT 1 10.000
TEXT 1 20.000
TEXT 2 30.000
TEXT 2 40.000
TEXT 2 50.000
TEXT 3 60.000
etc
In an other workbook called "Jobspecs" I want to count the quantities
when cell O2 is for example text 1

I have this formule, but the cell is not calculating right:
=SUMIF('[ORDER STATUS 2011.xlsm]Order Status 2011'!$A$2:$H
$10000;O2;'[ORDER STATUS 2011.xlsm]Order Status 2011'!$H:$H)

Note: workbook Order Status is always closed.

Who can help me with the correct formule?

 

[Berry]

What the main problem is when Order status workbook is closed, I get
an #VALUE! Error
When workbook is open, the formule is working:

=SUMIF('[ORDER STATUS 2011.xlsm]Order Status 2011'!$A:$A;O2;'[ORDER
STATUS 2011.xlsm]Order Status 2011'!$H:$H)

 

[Dave Peterson]

There are some functions that won't work if the sending workbook is closed.
=sumif(), =countif(), =indirect() are a few.

But =sumproduct() will work ok.

=sumproduct(--('[ORDER STATUS 2011.xlsm]Order Status 2011'!$A:$A=o2),
'[ORDER STATUS 2011.xlsm]Order Status 2011'!$H:$H)

I'd build the formula with the sending workbook open to avoid lots of typing.

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

What the main problem is when Order status workbook is closed, I get
an #VALUE! Error
When workbook is open, the formule is working:

=SUMIF('[ORDER STATUS 2011.xlsm]Order Status 2011'!$A:$A;O2;'[ORDER
STATUS 2011.xlsm]Order Status 2011'!$H:$H)

 

[Berry]

This is working great!!
Thanks a lot.

Best wishes for 2011!