sumproduct assistance pleas

Excellent I used option 2 and created a new column. This has worked a big
thanks.
Cheers
Ajay

"Fred Smith" wrote:

There are a few ways:
1. Use Text to Columns to convert text to a date (you can create a new
column, or simply replace the old one).
2. Force Excel to convert the text to a number by doing arithmetic on it.
Put 1 in an empty cell. Copy it. Highlight your column, right-click, choose
Paste Special...Multiply)
3. Tell Sumproduct to convert the text to a date. Try using:
=SUMPRODUCT('Airlines Derby Dim and Inst
all'!$B$2:$B$2116=Summary!A2)*(Datevalue('Airlines Derby Dim and Inst
all'!$L$2:$L$2116)DATE(2009,6,17))

Regards,
Fred

"Ajay" wrote in message
...
Hi
I looked at your two suggestions and your right the 2nd one returns false
against all of the dates. How do I change the format so that they are
registered and picked up in the summary sheet calculation please
A big thanks
Ajay

"Fred Smith" wrote:

Two things to look at:
1. What's in Summary!A2?
2. Are you sure column L contains true Excel dates? Checking the
formatting
doesn't proved anything because they can look like dates but still be
text.
Check with =isnumber(l2) and copy down. If any return False, that's your
problem.

Regards,
Fred.

"Ajay" wrote in message
...
Hello
Thankyou for your help I have replaced my formula with the one below
and I
still get a 0 count in all depts which I know is wrong.

The formula I am using is
=SUMPRODUCT('Airlines Derby Dim and Inst
all'!$B$2:$B$2116=Summary!A2)*('Airlines Derby Dim and Inst
all'!$L$2:$L$2116DATE(2009,6,17))

I have checked the format of the date column in the raw data sheet and
that
is correct.

Any other ideas please?
Ajay

"Bernard Liengme" wrote:

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:L2000Date(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:L17/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

Glad I could help. Thanks for the feedback.

Fred.

"Ajay" wrote in message
...
Excellent I used option 2 and created a new column. This has worked a big
thanks.
Cheers
Ajay

"Fred Smith" wrote:

There are a few ways:
1. Use Text to Columns to convert text to a date (you can create a new
column, or simply replace the old one).
2. Force Excel to convert the text to a number by doing arithmetic on it.
Put 1 in an empty cell. Copy it. Highlight your column, right-click,
choose
Paste Special...Multiply)
3. Tell Sumproduct to convert the text to a date. Try using:
=SUMPRODUCT('Airlines Derby Dim and Inst
all'!$B$2:$B$2116=Summary!A2)*(Datevalue('Airlines Derby Dim and Inst
all'!$L$2:$L$2116)DATE(2009,6,17))

Regards,
Fred

"Ajay" wrote in message
...
Hi
I looked at your two suggestions and your right the 2nd one returns
false
against all of the dates. How do I change the format so that they are
registered and picked up in the summary sheet calculation please
A big thanks
Ajay

"Fred Smith" wrote:

Two things to look at:
1. What's in Summary!A2?
2. Are you sure column L contains true Excel dates? Checking the
formatting
doesn't proved anything because they can look like dates but still be
text.
Check with =isnumber(l2) and copy down. If any return False, that's
your
problem.

Regards,
Fred.

"Ajay" wrote in message
...
Hello
Thankyou for your help I have replaced my formula with the one below
and I
still get a 0 count in all depts which I know is wrong.

The formula I am using is
=SUMPRODUCT('Airlines Derby Dim and Inst
all'!$B$2:$B$2116=Summary!A2)*('Airlines Derby Dim and Inst
all'!$L$2:$L$2116DATE(2009,6,17))

I have checked the format of the date column in the raw data sheet
and
that
is correct.

Any other ideas please?
Ajay

"Bernard Liengme" wrote:

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:L2000Date(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:L17/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