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:G32>E3: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

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

Regards,
Fred

"adimar" <(E-Mail Removed)> wrote in message
news:C31FD738-5A24-45A7-8DAB-(E-Mail Removed)...
>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:G32>E3: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

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:G32>E3: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" <(E-Mail Removed)> wrote in message
> news:C31FD738-5A24-45A7-8DAB-(E-Mail Removed)...
> >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:G32>E3: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
>
>

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" <(E-Mail Removed)> wrote in message
news:78738783-9470-41AD-9D22-(E-Mail Removed)...
>
> 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:G32>E3: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" <(E-Mail Removed)> wrote in message
>> news:C31FD738-5A24-45A7-8DAB-(E-Mail Removed)...
>> >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:G32>E3: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
>>
>>

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
=C22>B22
TRUE/FALSE as above
=COUNTIF(D2231, "CLOSED")
10
=SUMPRODUCT((D2231="CLOSED")*1) 10
=SUMPRODUCT((C22:C31>B22:B31)*(C22:C31="CLOSED")) 0
=SUMPRODUCT(C22:C31>B22: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
>

In the second formula, the range is wrong. It should be (D2231="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" <(E-Mail Removed)> wrote in message
news:71058362-C52C-4D17-83C6-(E-Mail Removed)...
>
> 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
> =C22>B22
> TRUE/FALSE as above
> =COUNTIF(D2231, "CLOSED")
> 10
> =SUMPRODUCT((D2231="CLOSED")*1) 10
> =SUMPRODUCT((C22:C31>B22:B31)*(C22:C31="CLOSED")) 0
> =SUMPRODUCT(C22:C31>B22: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
>>

Works fine now... Thank you.

"Fred Smith" wrote:

> In the second formula, the range is wrong. It should be (D2231="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" <(E-Mail Removed)> wrote in message
> news:71058362-C52C-4D17-83C6-(E-Mail Removed)...
> >
> > 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
> > =C22>B22
> > TRUE/FALSE as above
> > =COUNTIF(D2231, "CLOSED")
> > 10
> > =SUMPRODUCT((D2231="CLOSED")*1) 10
> > =SUMPRODUCT((C22:C31>B22:B31)*(C22:C31="CLOSED")) 0
> > =SUMPRODUCT(C22:C31>B22: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
> >>
>
>