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

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

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