Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default 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


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default 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



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
Equal formula keeping both content and format Laetitia Excel Worksheet Functions 9 January 12th 08 09:38 AM
How to maintain merged cell format when cutting the content away ifjoe Excel Worksheet Functions 2 January 1st 07 02:04 PM
how do I create a link for both content and format of the cell? Becky Excel Worksheet Functions 1 October 27th 06 01:43 AM
replace one tag in content of one cell and format not changed replace one tag in content of one cell a Excel Discussion (Misc queries) 1 January 9th 06 04:36 PM
How do I copy a cell (content AND format) from one worksheet to a. Excel Format Copy Excel Worksheet Functions 1 February 9th 05 11:34 PM


All times are GMT +1. The time now is 02:30 PM.

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

About Us

"It's about Microsoft Excel"