I have created a conditional array function that sums values in column K between two dates. When I break the formula up, the individual parts seem to work. However, together I get a #value! reference. The formula is below. Note, the dates in column A are a text string, so I used datevalue and left functions to tease out the date from the text {[string.
{=SUMPRODUCT(($A$4:$A$597<>"")*(DATEVALUE(LEFT($A$4:$A$597,FIND(" ",$A$4:$A$597)-1))>=$D$1182)*(DATEVALUE(LEFT($A$4:$A$597,FIND(" ",$A$4:$A$597)-1))<=$E$1182)*K$4:K$597)}
If anyone has ideas on how to get this function to work, I would be very appreciative.
Thanks,
Chet
{=SUMPRODUCT(($A$4:$A$597<>"")*(DATEVALUE(LEFT($A$4:$A$597,FIND(" ",$A$4:$A$597)-1))>=$D$1182)*(DATEVALUE(LEFT($A$4:$A$597,FIND(" ",$A$4:$A$597)-1))<=$E$1182)*K$4:K$597)}
If anyone has ideas on how to get this function to work, I would be very appreciative.
Thanks,
Chet