Home 
Search 
Today's Posts 
#1




SUMPRODUCT CAPTURING DATA FROM ANOTHER SPREADSHEET
Excel 2003
I have two spreadsheets within a workbook (Shift 1 and Daily Worksheet). I want to capture each category (i.e. 2MAKE READY)in Shift 1 spreadsheet for each day of the month. The formula is in the Daily Worksheet which has the categories in column a, and columns b  z have the day of the month (i.e.10/4/04) and this is where I have the formula: =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2MAKE READY for the month by eliminating everything from *on, but I can't get it to match and count on the date. What I'd really like {: ) is to look up the 2MAKE READY in the SHIFT 1 and count if the date = b1,c1 (10/1, 10/2) cells in the Daily Worksheet. I hope this makes sense! TIA, Carole O 
#2




Hi
try: =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=DATE(2004,10,4)))  Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Excel 2003 I have two spreadsheets within a workbook (Shift 1 and Daily Worksheet). I want to capture each category (i.e. 2MAKE READY)in Shift 1 spreadsheet for each day of the month. The formula is in the Daily Worksheet which has the categories in column a, and columns b  z have the day of the month (i.e.10/4/04) and this is where I have the formula: =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2MAKE READY for the month by eliminating everything from *on, but I can't get it to match and count on the date. What I'd really like {: ) is to look up the 2MAKE READY in the SHIFT 1 and count if the date = b1,c1 (10/1, 10/2) cells in the Daily Worksheet. I hope this makes sense! TIA, Carole O 
#3




Hi, Frank
I copied and pasted your suggestion and get a #REF. When I trace the error, it shows a small table (Shift 1?) with an arrow pointing to the #REF cell. Any ideas? Carole O "Frank Kabel" wrote: Hi try: =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=DATE(2004,10,4)))  Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Excel 2003 I have two spreadsheets within a workbook (Shift 1 and Daily Worksheet). I want to capture each category (i.e. 2MAKE READY)in Shift 1 spreadsheet for each day of the month. The formula is in the Daily Worksheet which has the categories in column a, and columns b  z have the day of the month (i.e.10/4/04) and this is where I have the formula: =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2MAKE READY for the month by eliminating everything from *on, but I can't get it to match and count on the date. What I'd really like {: ) is to look up the 2MAKE READY in the SHIFT 1 and count if the date = b1,c1 (10/1, 10/2) cells in the Daily Worksheet. I hope this makes sense! TIA, Carole O 
#4




Hi
maybe a linebreak as I only copied your sheet references. Just use your existing formula but with the dATE function included  Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Hi, Frank I copied and pasted your suggestion and get a #REF. When I trace the error, it shows a small table (Shift 1?) with an arrow pointing to the #REF cell. Any ideas? Carole O "Frank Kabel" wrote: Hi try: =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=DATE(2004,10,4)))  Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Excel 2003 I have two spreadsheets within a workbook (Shift 1 and Daily Worksheet). I want to capture each category (i.e. 2MAKE READY)in Shift 1 spreadsheet for each day of the month. The formula is in the Daily Worksheet which has the categories in column a, and columns b  z have the day of the month (i.e.10/4/04) and this is where I have the formula: =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2MAKE READY for the month by eliminating everything from *on, but I can't get it to match and count on the date. What I'd really like {: ) is to look up the 2MAKE READY in the SHIFT 1 and count if the date = b1,c1 (10/1, 10/2) cells in the Daily Worksheet. I hope this makes sense! TIA, Carole O 
#5




Frank has showed you what the problem is  10/4/2004 in this context means 10
divided by 4 divided by 2004, not the date Oct 4 2004. In addition to embedding the literal date parameters inside the DATE formula, you can also put the date in another cell (or perhaps you have it in a cell already), then use a reference to that cell instead of the DATE formula. e.g. if A1 contains the date 10/4/2004, =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=$A$1)) On Wed, 27 Oct 2004 12:23:03 0700, "Carole O" wrote: Excel 2003 I have two spreadsheets within a workbook (Shift 1 and Daily Worksheet). I want to capture each category (i.e. 2MAKE READY)in Shift 1 spreadsheet for each day of the month. The formula is in the Daily Worksheet which has the categories in column a, and columns b  z have the day of the month (i.e.10/4/04) and this is where I have the formula: =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2MAKE READY for the month by eliminating everything from *on, but I can't get it to match and count on the date. What I'd really like {: ) is to look up the 2MAKE READY in the SHIFT 1 and count if the date = b1,c1 (10/1, 10/2) cells in the Daily Worksheet. I hope this makes sense! TIA, Carole O 
#6




Wahoo!!! I'm doing a victory dance in my cubicle!! Thank you both so much.
This is exactly what I wanted (besides circumventing the pivot table!!) Carole O "Myrna Larson" wrote: Frank has showed you what the problem is  10/4/2004 in this context means 10 divided by 4 divided by 2004, not the date Oct 4 2004. In addition to embedding the literal date parameters inside the DATE formula, you can also put the date in another cell (or perhaps you have it in a cell already), then use a reference to that cell instead of the DATE formula. e.g. if A1 contains the date 10/4/2004, =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=$A$1)) On Wed, 27 Oct 2004 12:23:03 0700, "Carole O" wrote: Excel 2003 I have two spreadsheets within a workbook (Shift 1 and Daily Worksheet). I want to capture each category (i.e. 2MAKE READY)in Shift 1 spreadsheet for each day of the month. The formula is in the Daily Worksheet which has the categories in column a, and columns b  z have the day of the month (i.e.10/4/04) and this is where I have the formula: =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2MAKE READY for the month by eliminating everything from *on, but I can't get it to match and count on the date. What I'd really like {: ) is to look up the 2MAKE READY in the SHIFT 1 and count if the date = b1,c1 (10/1, 10/2) cells in the Daily Worksheet. I hope this makes sense! TIA, Carole O 
#7




Hi
glad you sorted it out :) Maybe for further reference: http://www.xldynamic.com/source/xld.SUMPRODUCT.html  Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Wahoo!!! I'm doing a victory dance in my cubicle!! Thank you both so much. This is exactly what I wanted (besides circumventing the pivot table!!) Carole O "Myrna Larson" wrote: Frank has showed you what the problem is  10/4/2004 in this context means 10 divided by 4 divided by 2004, not the date Oct 4 2004. In addition to embedding the literal date parameters inside the DATE formula, you can also put the date in another cell (or perhaps you have it in a cell already), then use a reference to that cell instead of the DATE formula. e.g. if A1 contains the date 10/4/2004, =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=$A$1)) On Wed, 27 Oct 2004 12:23:03 0700, "Carole O" wrote: Excel 2003 I have two spreadsheets within a workbook (Shift 1 and Daily Worksheet). I want to capture each category (i.e. 2MAKE READY)in Shift 1 spreadsheet for each day of the month. The formula is in the Daily Worksheet which has the categories in column a, and columns b  z have the day of the month (i.e.10/4/04) and this is where I have the formula: =SUMPRODUCT(('SHIFT 1'!$B$2:$B$200="2MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2MAKE READY for the month by eliminating everything from *on, but I can't get it to match and count on the date. What I'd really like {: ) is to look up the 2MAKE READY in the SHIFT 1 and count if the date = b1,c1 (10/1, 10/2) cells in the Daily Worksheet. I hope this makes sense! TIA, Carole O 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Help with data not getting plotted  Excel Discussion (Misc queries)  
copying data from Excel spreadsheet to another  Excel Discussion (Misc queries)  
Importing Data From Another Spreadsheet  Excel Discussion (Misc queries)  
Entering data on template and then data going to a spreadsheet.  Excel Discussion (Misc queries)  
Running Data Table using an input that triggers DDE linked data  Excel Discussion (Misc queries) 