count in weeks - pls help

[ambcw]

Hi Guys,
can anybody help me this please..your input is much appreciated.
Below is the formula that I used to count completed properties for the month of march, year 2011 and it works well. however I was asked to count on weekly basis, say from 1 mar 11 to 7 mar 11, and so on.
=SUMPRODUCT(--($AT$3:$AT$10000=$P$8),--(MONTH($BX$3:$BX$10000)=2),--(YEAR($BX$3:$BX$10000)=2011))
Also, I don't know if there's another way of getting this automatic update. what happen now is I will change the month each month to suit.

Any input would be very much appreciated.

Cheers
Andre

 

[Aladin Akyurek]

Hi Guys,
can anybody help me this please..your input is much appreciated.
Below is the formula that I used to count completed properties for the month of march, year 2011 and it works well. however I was asked to count on weekly basis, say from 1 mar 11 to 7 mar 11, and so on.
=SUMPRODUCT(--($AT$3:$AT$10000=$P$8),--(MONTH($BX$3:$BX$10000)=2),--(YEAR($BX$3:$BX$10000)=2011))
Also, I don't know if there's another way of getting this automatic update. what happen now is I will change the month each month to suit.

Any input would be very much appreciated.

Cheers
Andre

Month-based calcs (counts)...

A2: 1-Feb-11
A3: 1-Mar-11

A2 means the month/year pair of Feb/11.

B2, copy down:

=SUMPRODUCT(--($AT$3:$AT$10000=$P$8),--($BX$3:$BX$10000-DAY($BX$3:$BX$10000)+1=$A2))

Week-based calcs (counts)

E2: 1-Mar-11
F2: 7-Mar-11

G2:

=SUMPRODUCT(--($AT$3:$AT$10000=$P$8),--($BX$3:$BX$10000>=$E2),--($BX$3:$BX$10000<=$F2))

 

[ambcw]

Hi Aladin,

Thanks for the reply. I'm trying to understand your input but couldn't get it well, maybe I'm too weak in excel.

I have a column for completion date, in this case

BX
3 1 mar 11
4 3 mar 11
5 12 mar 11
6 16 mar 11
7 17 mar 11
to .31 mar 11
10,000

then I want to count how many was done for week 1, week 2, week 3 and week 4

in example above
for week 1 = 2
week 2 = 1
week 3 = 2
week 4 = 0

Thanks for your help.

 

[Aladin Akyurek]

Hi Aladin,

Thanks for the reply. I'm trying to understand your input but couldn't get it well, maybe I'm too weak in excel.

I have a column for completion date, in this case

BX
3 1 mar 11
4 3 mar 11
5 12 mar 11
6 16 mar 11
7 17 mar 11
to .31 mar 11
10,000

then I want to count how many was done for week 1, week 2, week 3 and week 4

in example above
for week 1 = 2
week 2 = 1
week 3 = 2
week 4 = 0

Thanks for your help.

Enter the date criteria in a convenient area, say, in E:G...

E2: Week 1

F2: 1-Mar-11

G2: 7-Mar-11

E3: Week 2

F3: 8-Mar-11

G3: 14-Mar-11

Assuming that P8 must still be included as criterion:

In H2 enter and copy down:

=SUMPRODUCT(--($AT$3:$AT$10000=$P$8),--($BX$3:$BX$10000>=$F2),--($BX$3:$BX$10000<=$G2))

Does this help?

 

[ambcw]

Hi Aladin,

Thanks for your help. Much appreciated.

Regards,
Andre

 

[Aladin Akyurek]

Hi Aladin,

Thanks for your help. Much appreciated.

Regards,
Andre

Great that it's all sorted out. Thanks for the feedback.