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 