Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct with dates | Excel Worksheet Functions | |||
sumproduct with dates | New Users to Excel | |||
sumproduct & dates | Excel Worksheet Functions | |||
Sumproduct and dates | New Users to Excel | |||
sumproduct between dates | Excel Worksheet Functions |