Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 27th 04, 08:23 PM
Carole O
 
Posts: n/a
Default 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




  #2   Report Post  
Old October 27th 04, 08:34 PM
Frank Kabel
 
Posts: n/a
Default

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




  #3   Report Post  
Old October 27th 04, 08:49 PM
Carole O
 
Posts: n/a
Default

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





  #4   Report Post  
Old October 27th 04, 08:53 PM
Frank Kabel
 
Posts: n/a
Default

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






  #5   Report Post  
Old October 27th 04, 08:54 PM
Myrna Larson
 
Posts: n/a
Default

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





  #6   Report Post  
Old October 27th 04, 09:29 PM
Carole O
 
Posts: n/a
Default

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




  #7   Report Post  
Old October 27th 04, 09:36 PM
Frank Kabel
 
Posts: n/a
Default

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with data not getting plotted Scott Ehrlich Excel Discussion (Misc queries) 2 January 25th 05 05:17 AM
copying data from Excel spreadsheet to another DavidB Excel Discussion (Misc queries) 1 January 10th 05 11:26 PM
Importing Data From Another Spreadsheet Tiziano Excel Discussion (Misc queries) 6 January 7th 05 02:35 AM
Entering data on template and then data going to a spreadsheet. KJH Excel Discussion (Misc queries) 3 December 24th 04 01:04 AM
Running Data Table using an input that triggers DDE linked data [email protected] Excel Discussion (Misc queries) 1 December 16th 04 11:56 AM


All times are GMT +1. The time now is 06:23 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017