|
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. 2-MAKE 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="2-MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2-MAKE 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 2-MAKE 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 |
Hi
try: =SUMPRODUCT(--('SHIFT 1'!$B$2:$B$200="2-MAKE 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. 2-MAKE 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="2-MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2-MAKE 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 2-MAKE 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 |
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="2-MAKE 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. 2-MAKE 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="2-MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2-MAKE 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 2-MAKE 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 |
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="2-MAKE 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. 2-MAKE 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="2-MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2-MAKE 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 2-MAKE 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 |
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="2-MAKE 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. 2-MAKE 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="2-MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2-MAKE 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 2-MAKE 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 |
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="2-MAKE 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. 2-MAKE 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="2-MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2-MAKE 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 2-MAKE 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 |
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="2-MAKE 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. 2-MAKE 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="2-MAKE READY")*('SHIFT 1'!$C$2:$C$200=10/4/2004)). This isn't working. I can get the total of all 2-MAKE 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 2-MAKE 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 |
| All times are GMT +1. The time now is 06:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com