Date value content format
 

I have the data below in different spreadsheets. I'm attempting to debug
"sumproduct" errors on one ofthe sheet. Tha same formula works fine in
another sheet.

=SUMPRODUCT((H3:H32="CLOSED")*(G3:G32E3:E32))

11/19/07 0:00 11/19/07 0:00 11/2/07 19:10
11/19/07 0:00 11/12/07 23:59 11/12/07 16:25
11/19/07 0:00 11/12/07 23:59 11/12/07 16:25
11/26/07 0:00 11/21/07 23:59 11/20/07 15:47
11/26/07 0:00 11/20/07 23:59 11/20/07 16:05
11/26/07 0:00 11/21/07 23:59 11/20/07 17:03

Date value content format
 

And the problem is? And the solution you are looking for is?

Regards,
Fred

"adimar" wrote in message
...
I have the data below in different spreadsheets. I'm attempting to debug
"sumproduct" errors on one ofthe sheet. Tha same formula works fine in
another sheet.

=SUMPRODUCT((H3:H32="CLOSED")*(G3:G32E3:E32))

11/19/07 0:00 11/19/07 0:00 11/2/07 19:10
11/19/07 0:00 11/12/07 23:59 11/12/07 16:25
11/19/07 0:00 11/12/07 23:59 11/12/07 16:25
11/26/07 0:00 11/21/07 23:59 11/20/07 15:47
11/26/07 0:00 11/20/07 23:59 11/20/07 16:05
11/26/07 0:00 11/21/07 23:59 11/20/07 17:03

Date value content format
 

Problem: The formula below returns correct value in one sheet and zero in
another, on what "seems" to be the same data. How do I check date cell
content is really the same in different sheets?

SUMPRODUCT((H3:H32="CLOSED")*(G3:G32E3:E32))

I am looking for a correct way of comparing dates.


Thank you.


"Fred Smith" wrote:

And the problem is? And the solution you are looking for is?

Regards,
Fred

"adimar" wrote in message
...
I have the data below in different spreadsheets. I'm attempting to debug
"sumproduct" errors on one ofthe sheet. Tha same formula works fine in
another sheet.

=SUMPRODUCT((H3:H32="CLOSED")*(G3:G32E3:E32))

11/19/07 0:00 11/19/07 0:00 11/2/07 19:10
11/19/07 0:00 11/12/07 23:59 11/12/07 16:25
11/19/07 0:00 11/12/07 23:59 11/12/07 16:25
11/26/07 0:00 11/21/07 23:59 11/20/07 15:47
11/26/07 0:00 11/20/07 23:59 11/20/07 16:05
11/26/07 0:00 11/21/07 23:59 11/20/07 17:03

Date value content format
 

There must be a difference in the data. Things to look for:

1. Do you have "CLOSED" in at least one of the cells in H3:H32?
2. Is at least one of the cells in G3:G32 greater than E3:E32?
3. Are G3:G32 and E3:E32 true dates, or are they text masquerading as dates?
(Easy test -- try to change the format of a cell)?

If the formula works on one spreadsheet, but not on another, then the data
must be different.

Regards,
Fred

"adimar" wrote in message
...

Problem: The formula below returns correct value in one sheet and zero in
another, on what "seems" to be the same data. How do I check date cell
content is really the same in different sheets?

SUMPRODUCT((H3:H32="CLOSED")*(G3:G32E3:E32))

I am looking for a correct way of comparing dates.


Thank you.


"Fred Smith" wrote:

And the problem is? And the solution you are looking for is?

Regards,
Fred

"adimar" wrote in message
...
I have the data below in different spreadsheets. I'm attempting to debug
"sumproduct" errors on one ofthe sheet. Tha same formula works fine in
another sheet.

=SUMPRODUCT((H3:H32="CLOSED")*(G3:G32E3:E32))

11/19/07 0:00 11/19/07 0:00 11/2/07 19:10
11/19/07 0:00 11/12/07 23:59 11/12/07 16:25
11/19/07 0:00 11/12/07 23:59 11/12/07 16:25
11/26/07 0:00 11/21/07 23:59 11/20/07 15:47
11/26/07 0:00 11/20/07 23:59 11/20/07 16:05
11/26/07 0:00 11/21/07 23:59 11/20/07 17:03

Date value content format
 

On this data,
B C D
E
12/3/07 12:00 AM 12/26/07 4:18 PM CLOSED TRUE
12/3/07 12:00 AM 11/30/07 5:51 PM CLOSED FALSE
12/6/07 12:00 AM 11/16/07 4:37 PM CLOSED FALSE
11/19/07 12:00 AM 11/22/07 7:10 PM CLOSED TRUE
11/19/07 12:00 AM 11/12/07 4:25 PM CLOSED FALSE
11/19/07 12:00 AM 11/24/07 4:25 PM CLOSED TRUE
11/26/07 12:00 AM 11/20/07 3:47 PM CLOSED FALSE
11/26/07 12:00 AM 11/29/07 4:05 PM CLOSED TRUE
11/26/07 12:00 AM 11/20/07 5:03 PM CLOSED FALSE
12/7/07 10:16 PM 11/30/07 5:08 PM CLOSED FALSE

I ran these formulas with these results:

Formula
Result
=C22B22
TRUE/FALSE as above
=COUNTIF(D22:D31, "CLOSED")
10
=SUMPRODUCT((D22:D31="CLOSED")*1) 10
=SUMPRODUCT((C22:C31B22:B31)*(C22:C31="CLOSED")) 0
=SUMPRODUCT(C22:C31B22:B31) 0

First 2 are correct, last 2 are not.
Copied to a blank workbook and got the same results.
Also copied text from window above and pasted special, text. Same result.
What else can I try?

Thank you.

"Fred Smith" wrote:

There must be a difference in the data. Things to look for:

1. Do you have "CLOSED" in at least one of the cells in H3:H32?
2. Is at least one of the cells in G3:G32 greater than E3:E32?
3. Are G3:G32 and E3:E32 true dates, or are they text masquerading as dates?
(Easy test -- try to change the format of a cell)?

If the formula works on one spreadsheet, but not on another, then the data
must be different.

Regards,
Fred

Date value content format
 

In the second formula, the range is wrong. It should be (D22:D31="CLOSED"),
rather than C22:C31.

In the third formula, you are not converting the true/false results to a
number. Either multiply by 1 (as you did in the first formula), or use the
more common double unary (--) to force the conversion.

Hope this helps,
Fred.

"adimar" wrote in message
...

On this data,
B C D
E
12/3/07 12:00 AM 12/26/07 4:18 PM CLOSED TRUE
12/3/07 12:00 AM 11/30/07 5:51 PM CLOSED FALSE
12/6/07 12:00 AM 11/16/07 4:37 PM CLOSED FALSE
11/19/07 12:00 AM 11/22/07 7:10 PM CLOSED TRUE
11/19/07 12:00 AM 11/12/07 4:25 PM CLOSED FALSE
11/19/07 12:00 AM 11/24/07 4:25 PM CLOSED TRUE
11/26/07 12:00 AM 11/20/07 3:47 PM CLOSED FALSE
11/26/07 12:00 AM 11/29/07 4:05 PM CLOSED TRUE
11/26/07 12:00 AM 11/20/07 5:03 PM CLOSED FALSE
12/7/07 10:16 PM 11/30/07 5:08 PM CLOSED FALSE

I ran these formulas with these results:

Formula
Result
=C22B22
TRUE/FALSE as above
=COUNTIF(D22:D31, "CLOSED")
10
=SUMPRODUCT((D22:D31="CLOSED")*1) 10
=SUMPRODUCT((C22:C31B22:B31)*(C22:C31="CLOSED")) 0
=SUMPRODUCT(C22:C31B22:B31)
0

First 2 are correct, last 2 are not.
Copied to a blank workbook and got the same results.
Also copied text from window above and pasted special, text. Same result.
What else can I try?

Thank you.

"Fred Smith" wrote:

There must be a difference in the data. Things to look for:

1. Do you have "CLOSED" in at least one of the cells in H3:H32?
2. Is at least one of the cells in G3:G32 greater than E3:E32?
3. Are G3:G32 and E3:E32 true dates, or are they text masquerading as
dates?
(Easy test -- try to change the format of a cell)?

If the formula works on one spreadsheet, but not on another, then the
data
must be different.

Regards,
Fred

Date value content format
 

Works fine now... Thank you.

"Fred Smith" wrote:

In the second formula, the range is wrong. It should be (D22:D31="CLOSED"),
rather than C22:C31.

In the third formula, you are not converting the true/false results to a
number. Either multiply by 1 (as you did in the first formula), or use the
more common double unary (--) to force the conversion.

Hope this helps,
Fred.

"adimar" wrote in message
...

On this data,
B C D
E
12/3/07 12:00 AM 12/26/07 4:18 PM CLOSED TRUE
12/3/07 12:00 AM 11/30/07 5:51 PM CLOSED FALSE
12/6/07 12:00 AM 11/16/07 4:37 PM CLOSED FALSE
11/19/07 12:00 AM 11/22/07 7:10 PM CLOSED TRUE
11/19/07 12:00 AM 11/12/07 4:25 PM CLOSED FALSE
11/19/07 12:00 AM 11/24/07 4:25 PM CLOSED TRUE
11/26/07 12:00 AM 11/20/07 3:47 PM CLOSED FALSE
11/26/07 12:00 AM 11/29/07 4:05 PM CLOSED TRUE
11/26/07 12:00 AM 11/20/07 5:03 PM CLOSED FALSE
12/7/07 10:16 PM 11/30/07 5:08 PM CLOSED FALSE

I ran these formulas with these results:

Formula
Result
=C22B22
TRUE/FALSE as above
=COUNTIF(D22:D31, "CLOSED")
10
=SUMPRODUCT((D22:D31="CLOSED")*1) 10
=SUMPRODUCT((C22:C31B22:B31)*(C22:C31="CLOSED")) 0
=SUMPRODUCT(C22:C31B22:B31)
0

First 2 are correct, last 2 are not.
Copied to a blank workbook and got the same results.
Also copied text from window above and pasted special, text. Same result.
What else can I try?

Thank you.

"Fred Smith" wrote:

There must be a difference in the data. Things to look for:

1. Do you have "CLOSED" in at least one of the cells in H3:H32?
2. Is at least one of the cells in G3:G32 greater than E3:E32?
3. Are G3:G32 and E3:E32 true dates, or are they text masquerading as
dates?
(Easy test -- try to change the format of a cell)?

If the formula works on one spreadsheet, but not on another, then the
data
must be different.

Regards,
Fred