Count working days by week

[inta251]

Column A Column B
Date Week Number
Mon, Jan 08, 2007 2 =WEEKNUM(A2,1)
Mon, Jan 08, 2007 2 =WEEKNUM(A3,1)
Mon, Jan 08, 2007 2 =WEEKNUM(A4,1)
Tue, Jan 09, 2007 2 =WEEKNUM(A5,1)
Tue, Jan 09, 2007 2 and so on
Tue, Jan 09, 2007 2
Wed, Jan 10, 2007 2
Wed, Jan 10, 2007 2
Wed, Jan 10, 2007 2
Wed, Jan 10, 2007 2
Wed, Jan 10, 2007 2
Thu, Jan 11, 2007 2
Thu, Jan 11, 2007 2
Thu, Jan 11, 2007 2
Thu, Jan 11, 2007 2
Fri, Jan 12, 2007 2
Fri, Jan 12, 2007 2
Sun, Jan 14, 2007 3
Sun, Jan 14, 2007 3
Sun, Jan 14, 2007 3
Mon, Jan 15, 2007 3
Mon, Jan 15, 2007 3
Mon, Jan 15, 2007 3
Mon, Jan 15, 2007 3
and so on

So now I've to count working days in the weeks 1, 2, 3, ..........53
Same date entry as 1 day.
Something similar to count by month:
=SUMPRODUCT(--(YEAR(trips!$A$2:$A$1200)=2007),(MONTH(trips!$A$2:$A$1200)=1)
/COUNTIF(trips!$A$2:$A$1200,trips!$A$2:$A$1200&""))
Thanks in advance. Igor.

 

[Domenic]

Assuming a five day work week (Monday through Friday), let D2, D3, D4,
etc., contain 1, 2, 3, etc., then try...

E2, copied down:

=SUMPRODUCT(--(WEEKDAY($A$2:$A$25,2)<6),--($B$2:$B$25=D2))

To exclude holidays, let a range of cells contain a list of holidays,
let's say G2:G10, then try...

=SUMPRODUCT(--(WEEKDAY($A$2:$A$25,2)<6),--ISNA(MATCH($A$2:$A$25,$G$2:$G$1
0,0)),--($B$2:$B$25=D2))

Hope this helps!

 

[inta251 via OfficeKB.com]

Thanks for quick respond.
In both cases formulas count number of entrees in week 1, 2,….53.
I need count days per week in Column A
Now I’ll try simple. In Column A I have only dates.
Same dates entrees can be from 1 to infinity.

Sample: In this case
Date
01/08/2007
01/08/2007
01/08/2007 1st day
01/09/2007
01/09/2007
01/09/2007 2nd day
01/10/2007
01/10/2007
01/10/2007
01/10/2007
01/10/2007 3rd day
01/11/2007
01/11/2007
01/11/2007
01/11/2007 4th day
01/12/2007
01/12/2007 5th day
and so on

All this days belong to Week #2
This meet i worked 5 days in week 2.
Formula need with result 5.
Next Week #3,........53 (end of the year)
Thanks again. Igor.

 

[Domenic]

Try the following instead...

Assumptions:

A2:A100 contains the data

B2:B100 contains the week number

Formula:

=SUM(IF(FREQUENCY(IF($B$2:$B$100=D2,$A$2:$A$100),IF($B$2:$B$100=D2,$A$2:$
A$100)),1))

....where D2 contains the week number. Note that the formula needs to be
confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[inta251 via OfficeKB.com]

Still incorrect formula.
Temporarily I got formula
Week 1:
=SUMPRODUCT(--($A$2:$A$100>=DATE(2007,1,1)),--( $A$2:$A$100<=DATE(2007,1,6))
/COUNTIF($A$2:$A$100,$A$2:$A$100&""))

Week 2:
=SUMPRODUCT(--($A$2:$A$100>=DATE(2007,1,7)),--( $A$2:$A$100<=DATE(2007,1,13))
/COUNTIF($A$2:$A$100,$A$2:$A$100&""))

and so on.

If you will have some idea, please let me know.
Thanks again for your time.
Sincerely, Igor.

 

[Domenic]

I don't understand. Can you elaborate?

 

[inta251 via OfficeKB.com]

Hi, Domenic.
I have dates in ColumnA. I need count dates I worked in the week.
=WEEKNUM(A1) ------- result week#2
.............................................
Same dates count as one day.
All this dates belong to week #2, and in the formula I need result 5 working
days
row1 01/08/2007
row2 01/08/2007
row3 01/08/2007 1st day
row4 01/09/2007
row5 01/09/2007
row6 01/09/2007 2nd day
row7 01/10/2007
row8 01/10/2007
row9 01/10/2007
row10 01/10/2007
row11 01/10/2007 3rd day
row12 01/11/2007
row13 01/11/2007
row14 01/11/2007
row15 01/11/2007 4th day
row16 01/12/2007
row17 01/12/2007 5th day
and so on

 

[inta251 via OfficeKB.com]

Hi, Domenic.
Sorry for my dummies, but after few hours of thinking I realized formula
needs to be
confirmed with CTRL+SHIFT+ENTER, not just ENTER. After that in front and end
of the formula
show up two symbols { and }. After correct confirmation (CTRL+SHIFT+ENTER) I
got satisfied result.
Once again, thanks for your time and help to ‘DUMMIES’.

=SUM(IF(FREQUENCY(IF($B$2:$B$100=D2,$A$2:$A$100),IF($B$2:$B$100=D2,$A$2:$
A$100)),1))