Hi
I have tried your suggestion and am still at a loss as to why I cant get an
answer. I tried both your examples and no luck.
Any other suggestions please?
Ajay

"David Biddulph" wrote:

An extra pair of parentheses needed, I think.
Change =SUMPRODUCT('Airlines Derby Dim and Inst
all'!B1:B2000=Summary!A2)*('Airlines Derby Dim and Inst
all'!L1:L2000<Date(2009,6,17)) to
=SUMPRODUCT(('Airlines Derby Dim and Inst
all'!B1:B2000=Summary!A2)*('Airlines Derby Dim and Inst
all'!L1:L2000<Date(2009,6,17))) or
=SUMPRODUCT(('Airlines Derby Dim and Inst
all'!B1:B2000=Summary!A2),('Airlines Derby Dim and Inst
all'!L1:L2000<Date(2009,6,17))).
--
David Biddulph

"Bernard Liengme" wrote in message
...
1) Unless you have Excel 2007, SUMPRODUCT cannot use full column
references like B:B but needs something like B1:B2000

2) Excel will not understand the 17/6/2009 is a date but will compute 17
divided by 6 and then the result divided by 2009


Try
=SUMPRODUCT('Airlines Derby Dim and Inst
all'!B1:B2000=Summary!A2)*('Airlines Derby Dim and Inst
all'!L1:L2000<Date(2009,6,17)).

Tell us if you have luck with this
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Ajay" wrote in message
...
Afternoon all
I have a table of raw data containing an inventory list by department, I
need to count the number of items in each dept which are out of date.

I tried =SUMPRODUCT('Airlines Derby Dim and Inst
all'!B:B=Summary!A2)*('Airlines Derby Dim and Inst all'!L:L<17/6/2009).

Column B is the listing of all dept numbers and Column L is the date
information.
The summary sheet lists all the unique det numbers in column A.

I need to provide a count by dept with items containing dates before
today
(17th June). Hope that explains it
Thanks in advance
Ajay