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  |