Remember Me?

#### Menu

#1
October 27th 04, 08:23 PM
 Carole O Posts: n/a
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
October 27th 04, 08:34 PM
 Frank Kabel Posts: n/a

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
October 27th 04, 08:49 PM
 Carole O Posts: n/a

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
October 27th 04, 08:53 PM
 Frank Kabel Posts: n/a

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
October 27th 04, 08:54 PM
 Myrna Larson Posts: n/a

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
October 27th 04, 09:29 PM
 Carole O Posts: n/a

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
October 27th 04, 09:36 PM
 Frank Kabel Posts: n/a

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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Scott Ehrlich Excel Discussion (Misc queries) 2 January 25th 05 05:17 AM DavidB Excel Discussion (Misc queries) 1 January 10th 05 11:26 PM Tiziano Excel Discussion (Misc queries) 6 January 7th 05 02:35 AM KJH Excel Discussion (Misc queries) 3 December 24th 04 01:04 AM [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