SUMPRODUCT + DATE = 0

[nfbelo]

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<=DATE (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

 

[Guest]

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

 

[Aladin Akyurek]

=SUMPRODUCT(--(A1:A100>=DATE(2005;5;5));--(A1:A100<=DATE
(2005;5;9));B1:B100)

 

[Bob Phillips]

and most importantly, coerce the Booleans

SUMPRODUCT(--(A1:A100>=DATE(2005;5;5));--(A1:A100<=DATE
(2005;5;9));B1:B100)

 

[Guest]

You can't beat a bit of Boolean Coercion!!

 

[Bob Phillips]

You can if you get a big enough stick

You can't beat a bit of Boolean Coercion!!