SUMPRODUCT and Dates

[Glenn]

Greetings,
I am using the following
=SUMPRODUCT(($D$27:$D$500=C12)*($G$27:$G$500>=$K$2)*($G$27:$G$500<=$K$3)*($H$27:$H$500="Single")*($B$27:$B$500))
where C12 is a depot, Single refers to a size, either Single, Part or Full
and Column B is the column to sum. K2 and K3 refer to a start and finish
date. The formula appears to work well but I wondered if it was possible to
include the dates in the formula?

Glenn

 

[Mike H]

Glen,

You can do it like this but it's a retograde step. Almost invariably it's
better to have lookup values in cells rather than embedded in a formula. It
limits the chance of error.

=SUMPRODUCT(($D$27:$D$500=C12)*($G$27:$G$500>=DATE(2010,3,10))*($G$27:$G$500<=DATE(2010,3,10))*($H$27:$H$500="Single")*($B$27:$B$500))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Glenn]

Mike,

Excellent, many thanks.

Glenn

Glen,

You can do it like this but it's a retograde step. Almost invariably it's
better to have lookup values in cells rather than embedded in a formula. It
limits the chance of error.

=SUMPRODUCT(($D$27:$D$500=C12)*($G$27:$G$500>=DATE(2010,3,10))*($G$27:$G$500<=DATE(2010,3,10))*($H$27:$H$500="Single")*($B$27:$B$500))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.