Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct by specific weeks of the month or year
Hi everyone,
I been able to sucessfully find the number or projects my team has worked per month by using the following calculation (example is for August only): =SUMPRODUCT((--(MONTH(StatsCount)=8))*(StatsProduct=BD3)) Now I'd like to take it to the next level by find the number of projects my team has worked on per week, week after week, for the entire year. My team works 7 days a week not 5 and this calculation help them determine where to bill their time at the end of each week. Can you help me break down the above formula to give me weekly results? -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct by specific weeks of the month or year
Hi,
It would be nice to be able to write =sumproduct(weekenum(a1 etc but you can't, Sumproduct won't accept a range argument so it;s a bit more involved. Put the formula below in a cell and drag down 51 rows to sum each of the 52 weeks =SUMPRODUCT(--(1+INT(($A$2:$A$100-(DATE(YEAR($A$2:$A$100),1,2)-WEEKDAY(DATE(YEAR($A$2:$A$100),1,1))))/7)=ROW(A1))*(B$2:B$100=$H$1)) Mike "BLUV" wrote: Hi everyone, I been able to sucessfully find the number or projects my team has worked per month by using the following calculation (example is for August only): =SUMPRODUCT((--(MONTH(StatsCount)=8))*(StatsProduct=BD3)) Now I'd like to take it to the next level by find the number of projects my team has worked on per week, week after week, for the entire year. My team works 7 days a week not 5 and this calculation help them determine where to bill their time at the end of each week. Can you help me break down the above formula to give me weekly results? -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct by specific weeks of the month or year
OOPS,
Forgot you are using named ranges =SUMPRODUCT(--(1+INT((StatsCount-(DATE(YEAR(StatsCount),1,2)-WEEKDAY(DATE(YEAR(StatsCount),1,1))))/7)=ROW(A1))*(StatsProduct=$H$1)) Mike "Mike H" wrote: Hi, It would be nice to be able to write =sumproduct(weekenum(a1 etc but you can't, Sumproduct won't accept a range argument so it;s a bit more involved. Put the formula below in a cell and drag down 51 rows to sum each of the 52 weeks =SUMPRODUCT(--(1+INT(($A$2:$A$100-(DATE(YEAR($A$2:$A$100),1,2)-WEEKDAY(DATE(YEAR($A$2:$A$100),1,1))))/7)=ROW(A1))*(B$2:B$100=$H$1)) Mike "BLUV" wrote: Hi everyone, I been able to sucessfully find the number or projects my team has worked per month by using the following calculation (example is for August only): =SUMPRODUCT((--(MONTH(StatsCount)=8))*(StatsProduct=BD3)) Now I'd like to take it to the next level by find the number of projects my team has worked on per week, week after week, for the entire year. My team works 7 days a week not 5 and this calculation help them determine where to bill their time at the end of each week. Can you help me break down the above formula to give me weekly results? -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct by specific weeks of the month or year | Excel Programming | |||
sumproduct by criteria, month, & year | Excel Worksheet Functions | |||
SUMPRODUCT - Exclude LAST Row of Matched Criteria (Month & Year) | Excel Worksheet Functions | |||
sumproduct for month and year | Excel Discussion (Misc queries) | |||
SumProduct or Array Function for summing by month and year | Excel Programming |