Date Range as Criteria in an Array

[MathewS]

I need to use a date range from a date column as on of two
criterium for a Sum function, using an array.
example:
A - dates (one criteria)
B - quantity (second criteria)
C - price (column to sum, if both criterium are met)

my array formula so far:
=SUM((A12:A55>=1/1/2002)*(A12:A55<=1/31/2002)*(B12:B55)*
(C12:C55))

 

[Jason Morin]

Enclose your dates in double quotes and multiply by 1:

=SUM(..."1/1/2002"*1..."1/31/2002"*1...)

Wrapping the date in DATEVALUE also works. You can also
simply place the dates in cells and then reference those
cells.

HTH
Jason
Atlanta, GA

 

[MathewS]

Thanks, but it did not work. Here's my new formula
(array)...
=SUM((A13:A56>="1/1/2002"*1)*(A13:A56<="1/31/2002"*1)*
(B13:B56=1)*C13:C56)

 

[Jason Morin]

Works for me. Some ideas...

1) Are you pressing ctrl/shift/enter each time you edit
the formula cell?
2) Are you sure all the data in columns A,B,C are actually
numerical values and not text? Test them with ISTEXT.

Jason

 

[MathewS]

DOH! Having the correct year in the formula helped A LOT.
The "1/1/2002"*1 worked. Thanks!!!
(Why multiply by 1?)