Prev: Count Cells Within 10 of a Cell Value
Next: Averaging every 5th cell while omitting zeros
From: Glenn on 10 Mar 2010 15:13
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
From: Mike H on 10 Mar 2010 15:18
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" 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
From: Glenn on 10 Mar 2010 15:36
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$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" 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
 | 
Pages: 1
Prev: Count Cells Within 10 of a Cell Value
Next: Averaging every 5th cell while omitting zeros