|
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 |
|
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 |
=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 |
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 |
|
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 |
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 |
| All times are GMT +1. The time now is 09:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com