Perfect!!!

Thanks €“ one more question. How would the sumproduct look if I wanted to
match a range of dates from column A. Say the date range is Jan 8, 2006
through Jan 14, 2006.



"Bernard Liengme" wrote:

I will assume the first table to be on Sheet 1 in A1:C23
and the second to be on Sheet2 in A1:C4
On Sheet2 in C1 enter
=SUMPRODUCT(--(Sheet1!$B$1:$B$23=Sheet2!B1),--(Sheet1!$C$1:$C$23="Closed"))
Copy down the column
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim" wrote in message
...
Hello,

The help I need is in writing a formula that will count the number of
times
the store is listed (Column B) when it matches with closed (Column C).

On the table listed below I will return the data using a match.

From this table
A B C
1/8/2006 9:45 Store 1 Closed
1/8/2006 9:57 Store 2 Closed
1/8/2006 10:05 Store 3 Closed
1/8/2006 10:09 Store 4 Closed
1/8/2006 10:15 Store 5 Closed
1/8/2006 10:24 Store 1 Closed
1/8/2006 10:36 Store 2 In Progress
1/8/2006 10:41 Store 3 In Progress
1/8/2006 10:50 Store 4 Closed
1/8/2006 10:58 Store 5 Closed
1/8/2006 10:59 Store 1 Closed
1/8/2006 11:15 Store 2 Closed
1/8/2006 11:22 Store 3 In Progress
1/8/2006 11:24 Store 4 In Progress
1/8/2006 11:33 Store 5 Closed
1/8/2006 11:51 Store 1 Closed
1/8/2006 11:56 Store 2 Closed
1/8/2006 11:57 Store 3 Closed
1/8/2006 12:03 Store 4 Closed
1/8/2006 12:16 Store 5 Not Started
1/8/2006 12:23 Store 1 Closed
1/8/2006 12:28 Store 2 Closed
1/8/2006 12:57 Store 3 Closed

To this table

A B C
1/8/2006 9:45 Store 1 5
1/8/2006 9:57 Store 2 4
1/8/2006 10:05 Store 3 3
1/8/2006 10:09 Store 4 3

=SUMPRODUCT(--(range=Date(year(2006),month(1),day(8)),(--(range<=Date(year(2006),month(1),day(14)),.....)

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim" wrote in message
...
Perfect!!!

Thanks - one more question. How would the sumproduct look if I wanted to
match a range of dates from column A. Say the date range is Jan 8, 2006
through Jan 14, 2006.



"Bernard Liengme" wrote:

I will assume the first table to be on Sheet 1 in A1:C23
and the second to be on Sheet2 in A1:C4
On Sheet2 in C1 enter
=SUMPRODUCT(--(Sheet1!$B$1:$B$23=Sheet2!B1),--(Sheet1!$C$1:$C$23="Closed"))
Copy down the column
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim" wrote in message
...
Hello,

The help I need is in writing a formula that will count the number of
times
the store is listed (Column B) when it matches with closed (Column C).

On the table listed below I will return the data using a match.

From this table
A B C
1/8/2006 9:45 Store 1 Closed
1/8/2006 9:57 Store 2 Closed
1/8/2006 10:05 Store 3 Closed
1/8/2006 10:09 Store 4 Closed
1/8/2006 10:15 Store 5 Closed
1/8/2006 10:24 Store 1 Closed
1/8/2006 10:36 Store 2 In Progress
1/8/2006 10:41 Store 3 In Progress
1/8/2006 10:50 Store 4 Closed
1/8/2006 10:58 Store 5 Closed
1/8/2006 10:59 Store 1 Closed
1/8/2006 11:15 Store 2 Closed
1/8/2006 11:22 Store 3 In Progress
1/8/2006 11:24 Store 4 In Progress
1/8/2006 11:33 Store 5 Closed
1/8/2006 11:51 Store 1 Closed
1/8/2006 11:56 Store 2 Closed
1/8/2006 11:57 Store 3 Closed
1/8/2006 12:03 Store 4 Closed
1/8/2006 12:16 Store 5 Not Started
1/8/2006 12:23 Store 1 Closed
1/8/2006 12:28 Store 2 Closed
1/8/2006 12:57 Store 3 Closed

To this table

A B C
1/8/2006 9:45 Store 1 5
1/8/2006 9:57 Store 2 4
1/8/2006 10:05 Store 3 3
1/8/2006 10:09 Store 4 3

I bet Bernard meant:

=SUMPRODUCT(--(range=Date(2006,1,8)),(--(range<=Date(2006,1,14)),.....)

Year(2006) = 1905


Bernard Liengme wrote:

=SUMPRODUCT(--(range=Date(year(2006),month(1),day(8)),(--(range<=Date(year(2006),month(1),day(14)),.....)

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim" wrote in message
...
Perfect!!!

Thanks - one more question. How would the sumproduct look if I wanted to
match a range of dates from column A. Say the date range is Jan 8, 2006
through Jan 14, 2006.



"Bernard Liengme" wrote:

I will assume the first table to be on Sheet 1 in A1:C23
and the second to be on Sheet2 in A1:C4
On Sheet2 in C1 enter
=SUMPRODUCT(--(Sheet1!$B$1:$B$23=Sheet2!B1),--(Sheet1!$C$1:$C$23="Closed"))
Copy down the column
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim" wrote in message
...
Hello,

The help I need is in writing a formula that will count the number of
times
the store is listed (Column B) when it matches with closed (Column C).

On the table listed below I will return the data using a match.

From this table
A B C
1/8/2006 9:45 Store 1 Closed
1/8/2006 9:57 Store 2 Closed
1/8/2006 10:05 Store 3 Closed
1/8/2006 10:09 Store 4 Closed
1/8/2006 10:15 Store 5 Closed
1/8/2006 10:24 Store 1 Closed
1/8/2006 10:36 Store 2 In Progress
1/8/2006 10:41 Store 3 In Progress
1/8/2006 10:50 Store 4 Closed
1/8/2006 10:58 Store 5 Closed
1/8/2006 10:59 Store 1 Closed
1/8/2006 11:15 Store 2 Closed
1/8/2006 11:22 Store 3 In Progress
1/8/2006 11:24 Store 4 In Progress
1/8/2006 11:33 Store 5 Closed
1/8/2006 11:51 Store 1 Closed
1/8/2006 11:56 Store 2 Closed
1/8/2006 11:57 Store 3 Closed
1/8/2006 12:03 Store 4 Closed
1/8/2006 12:16 Store 5 Not Started
1/8/2006 12:23 Store 1 Closed
1/8/2006 12:28 Store 2 Closed
1/8/2006 12:57 Store 3 Closed

To this table

A B C
1/8/2006 9:45 Store 1 5
1/8/2006 9:57 Store 2 4
1/8/2006 10:05 Store 3 3
1/8/2006 10:09 Store 4 3





--

Dave Peterson