SUMPRODUCT and Dates
 

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

SUMPRODUCT and Dates
 

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$2 7:$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" wrote:

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

SUMPRODUCT and Dates
 

Mike,

Excellent, many thanks.

Glenn

"Mike H" wrote:

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$2 7:$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" wrote:

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