| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
SUMPRODUCT + DATE = 0
Hi,
I'm having this problem: A B C 05-May-05 2300 - 06-May-05 1500 - 07-May-05 2000 - 08-May-05 4100 - 09-May-05 3200 13100 10-May-05 2300 - .... I need to SUM periods like from 5 to 9 May in a column C, so I'm using this formula: SUMPRODUCT((A1:A100=DATE(2005;5;5));(A1:A100<=DAT E (2005;5;9));B1:B100) The problem is: SUMPRODUCT = 0 am I doing something wrong? Note that column A formats: DATE 14-Mar-98 Can you help me?? Thanks |
|
#2
|
|||
|
|||
|
Hi
Few ideas: You have used a semicolon instead of a comma in your formula. Make sure that your regional settings are right for using a comma. Make sure that your 'date' is a proper Excel date. Make sure your 'numbers' are proper numbers. You could try something simpler to check out your logic: =SUMPRODUCT((A1:A100=DATE(2005;5;5));B1:B100) should return 2300 -- Andy. "nfbelo" wrote in message ... Hi, I'm having this problem: A B C 05-May-05 2300 - 06-May-05 1500 - 07-May-05 2000 - 08-May-05 4100 - 09-May-05 3200 13100 10-May-05 2300 - ... I need to SUM periods like from 5 to 9 May in a column C, so I'm using this formula: SUMPRODUCT((A1:A100=DATE(2005;5;5));(A1:A100<=DAT E (2005;5;9));B1:B100) The problem is: SUMPRODUCT = 0 am I doing something wrong? Note that column A formats: DATE 14-Mar-98 Can you help me?? Thanks |
|
#3
|
|||
|
|||
|
=SUMPRODUCT(--(A1:A100=DATE(2005;5;5));--(A1:A100<=DATE
(2005;5;9));B1:B100) nfbelo wrote: Hi, I'm having this problem: A B C 05-May-05 2300 - 06-May-05 1500 - 07-May-05 2000 - 08-May-05 4100 - 09-May-05 3200 13100 10-May-05 2300 - ... I need to SUM periods like from 5 to 9 May in a column C, so I'm using this formula: SUMPRODUCT((A1:A100=DATE(2005;5;5));(A1:A100<=DAT E (2005;5;9));B1:B100) The problem is: SUMPRODUCT = 0 am I doing something wrong? Note that column A formats: DATE 14-Mar-98 Can you help me?? Thanks |
|
#4
|
|||
|
|||
|
and most importantly, coerce the Booleans
SUMPRODUCT(--(A1:A100=DATE(2005;5;5));--(A1:A100<=DATE (2005;5;9));B1:B100) -- HTH Bob Phillips <Andy B wrote in message ... Hi Few ideas: You have used a semicolon instead of a comma in your formula. Make sure that your regional settings are right for using a comma. Make sure that your 'date' is a proper Excel date. Make sure your 'numbers' are proper numbers. You could try something simpler to check out your logic: =SUMPRODUCT((A1:A100=DATE(2005;5;5));B1:B100) should return 2300 -- Andy. "nfbelo" wrote in message ... Hi, I'm having this problem: A B C 05-May-05 2300 - 06-May-05 1500 - 07-May-05 2000 - 08-May-05 4100 - 09-May-05 3200 13100 10-May-05 2300 - ... I need to SUM periods like from 5 to 9 May in a column C, so I'm using this formula: SUMPRODUCT((A1:A100=DATE(2005;5;5));(A1:A100<=DAT E (2005;5;9));B1:B100) The problem is: SUMPRODUCT = 0 am I doing something wrong? Note that column A formats: DATE 14-Mar-98 Can you help me?? Thanks |
|
#5
|
|||
|
|||
|
You can't beat a bit of Boolean Coercion!!
-- Andy. "Bob Phillips" wrote in message ... and most importantly, coerce the Booleans SUMPRODUCT(--(A1:A100=DATE(2005;5;5));--(A1:A100<=DATE (2005;5;9));B1:B100) -- HTH Bob Phillips <Andy B wrote in message ... Hi Few ideas: You have used a semicolon instead of a comma in your formula. Make sure that your regional settings are right for using a comma. Make sure that your 'date' is a proper Excel date. Make sure your 'numbers' are proper numbers. You could try something simpler to check out your logic: =SUMPRODUCT((A1:A100=DATE(2005;5;5));B1:B100) should return 2300 -- Andy. "nfbelo" wrote in message ... Hi, I'm having this problem: A B C 05-May-05 2300 - 06-May-05 1500 - 07-May-05 2000 - 08-May-05 4100 - 09-May-05 3200 13100 10-May-05 2300 - ... I need to SUM periods like from 5 to 9 May in a column C, so I'm using this formula: SUMPRODUCT((A1:A100=DATE(2005;5;5));(A1:A100<=DAT E (2005;5;9));B1:B100) The problem is: SUMPRODUCT = 0 am I doing something wrong? Note that column A formats: DATE 14-Mar-98 Can you help me?? Thanks |
|
#6
|
|||
|
|||
|
You can if you get a big enough stick :-)
<Andy B wrote in message ... You can't beat a bit of Boolean Coercion!! -- Andy. "Bob Phillips" wrote in message ... and most importantly, coerce the Booleans SUMPRODUCT(--(A1:A100=DATE(2005;5;5));--(A1:A100<=DATE (2005;5;9));B1:B100) -- HTH Bob Phillips <Andy B wrote in message ... Hi Few ideas: You have used a semicolon instead of a comma in your formula. Make sure that your regional settings are right for using a comma. Make sure that your 'date' is a proper Excel date. Make sure your 'numbers' are proper numbers. You could try something simpler to check out your logic: =SUMPRODUCT((A1:A100=DATE(2005;5;5));B1:B100) should return 2300 -- Andy. "nfbelo" wrote in message ... Hi, I'm having this problem: A B C 05-May-05 2300 - 06-May-05 1500 - 07-May-05 2000 - 08-May-05 4100 - 09-May-05 3200 13100 10-May-05 2300 - ... I need to SUM periods like from 5 to 9 May in a column C, so I'm using this formula: SUMPRODUCT((A1:A100=DATE(2005;5;5));(A1:A100<=DAT E (2005;5;9));B1:B100) The problem is: SUMPRODUCT = 0 am I doing something wrong? Note that column A formats: DATE 14-Mar-98 Can you help me?? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extract date from the most current date | Excel Discussion (Misc queries) | |||
| Date Math Problem | Excel Worksheet Functions | |||
| date and time | New Users to Excel | |||
| SUMPRODUCT with date range question | Excel Discussion (Misc queries) | |||
| Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Linear Mode
