Date Range as Criteria in an Array

  • Thread starter Thread starter MathewS
  • Start date Start date
M

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))
 
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
 
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)
 
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
 
DOH! Having the correct year in the formula helped A LOT.
The "1/1/2002"*1 worked. Thanks!!!
(Why multiply by 1?)
 
Back
Top