| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct assistance pleas
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct assistance pleas
Not tested but try DATEVALUE("17/6/2009") instead of plain 17/6/2009!
Regards, Stefi €žAjay€ť ezt Ă*rta: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct assistance pleas
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct assistance pleas
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$2116<DATE(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: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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct assistance pleas
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct assistance pleas
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$2116<DATE(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: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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct assistance pleas
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct assistance pleas
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$2116<DATE(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: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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct assistance pleas
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$2116<DATE(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: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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct assistance pleas
You haven't included your column L condition within the SUMPRODUCT, as
you've omitted a pair of parentheses. You need =SUMPRODUCT(('Airlines Derby Dim and Inst All'!$B$2:$B$2116=Summary!A2)*('Airlines Derby Dim and Inst All'!$L$2:$L$2116<DATE(2009,6,17))) not =SUMPRODUCT('Airlines Derby Dim and Inst All'!$B$2:$B$2116=Summary!A2)*('Airlines Derby Dim and Inst All'!$L$2:$L$2116<DATE(2009,6,17)) -- David Biddulph "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$2116<DATE(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: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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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$2116<DATE(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: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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct assistance pleas
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$2116<DATE(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: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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct assistance | Excel Worksheet Functions | |||
| SumProduct Assistance Please | Excel Discussion (Misc queries) | |||
| SUMPRODUCT assistance | Excel Worksheet Functions | |||
| Sumproduct assistance | Excel Worksheet Functions | |||
| pleas help: sumproduct function returns #value or #ref error | Excel Worksheet Functions | |||
Linear Mode
