SumProduct by specific week in a month

[BLUV]

Hi all,
I've successfully been able to identify which projects my team has worked on
during a monthly period. Using the following method (example below is for Aug
only):

=SUMPRODUCT((--(MONTH(StatsCount)=8))*(StatsProduct=BD3))

Now I would like to do the same thing but on a weekly basis. My team works
7 days a week so I don't need to work about cutting it down to Mon-Friday.
How can I take the above method and split it into 4 (maybe 5) seperate weekly
calculations?

 

[BLUV]

Also I need to do this week after week, for an entire year. So maybe the
formula has to be broken down into 52 different weeks. ????

 

[T. Valko]

You could test for a range of dates:

Week 1 = 8/1/2008 to 8/7/2008
Week 2 = 8/8/2008 to 8/14/2008
Week 3 = 8/15/2008 to 8/21/2008
Week 4 = 8/22/2008 to 8/28/2008
Week 5 = 8/28/2008 to EOM

A1 = 8/1/2008
B1 = 8/7/2008

=SUMPRODUCT(--(StatsCount>=A1),--(StatsCount<=B1),--(StatsProduct=BD3))

 

[David Biddulph]

Why the double unary minus? Doesn't the multiplication coerce the boolean
to numeric?

 

[muddan madhu]

check this

For 2nd week. i.e. 04-Jan-09 to 10-Jan-09 ( below formula will not
identify the year )

Array function

=SUMPRODUCT((MONTH(A1:A9)=1)*(IF(DAY(A1:A9)>=4,IF(DAY(A1:A9)
<=10,B1:B9))))

 

[muddan madhu]

array function

=SUMPRODUCT((MONTH(StatsCount)=1)*(IF(DAY(StatsCount)>=4,IF(DAY
(StatsCount)<=10,B1:B9))))

 

[BLUV]

The double unary minus if from another example someone gave me. I didn't
know what it meant until today. If take it out, will it speed up the time it
takes Excel to calculate my data? It should right? I don't think I need it.

 

[BLUV]

Would it make more sense to try and convert the days of the year to serial
numbers using the NOW() function, and then using the if >= to and <= logic?

 

[Dave Peterson]

That was David's point. It's not necessary.

It may speed up your calculation time, but I'd bet you won't notice it.