Work days per Week per year

[C_M2840]

Hi Excel Wizards;

Last year I was provided the following formula to count the number of dates entered per week in a data set.

=SUM(IF(FREQUENCY(IF($B$2:$B$999997=WEEKNUM($C1017),$C$2:$C$999997),$C$2:$C$999997),1))

Now I have 2 year worth of data in the spread sheet I need the formula to be able to separate 2017 dates per week and 2018 dates per week.

Colom A contains the year
Colom B contains the week #
Colom C contains the date

Thanks in advance for your help

 

[Oscar Cronquist]

Now I have 2 year worth of data in the spread sheet I need the formula to be able to separate 2017 dates per week and 2018 dates per week.

You mean count?

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Year[/td][td]Week[/td][td]Date[/td][td][/td][td]Year[/td][td]Week[/td][td]Count[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

2018​

[/td][td]

19​

[/td][td]

5/9/2018​

[/td][td][/td][td]

2018​

[/td][td]

19​

[/td][td]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

2018​

[/td][td]

26​

[/td][td]

6/28/2018​

[/td][td][/td][td]

2017​

[/td][td]

49​

[/td][td]

2​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

2018​

[/td][td]

26​

[/td][td]

6/30/2018​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

2018​

[/td][td]

19​

[/td][td]

5/12/2018​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

2018​

[/td][td]

21​

[/td][td]

5/26/2018​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

2018​

[/td][td]

19​

[/td][td]

5/8/2018​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

2017​

[/td][td]

49​

[/td][td]

12/3/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

2017​

[/td][td]

15​

[/td][td]

4/12/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

2017​

[/td][td]

35​

[/td][td]

8/29/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

2017​

[/td][td]

49​

[/td][td]

12/4/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

2017​

[/td][td]

23​

[/td][td]

6/7/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

2017​

[/td][td]

32​

[/td][td]

8/9/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

2017​

[/td][td]

38​

[/td][td]

9/19/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet19[/td][/tr][/table]

Formula in cell G2:
=SUMPRODUCT(($A$2:$A$14=E2)*($B$2:$B$14=F2))

 

[C_M2840]

Hi Oscar;

I need something similar to a count function however there are multiple data entries per day therefore simply counting doesn't work.

I need to count the number of unique dates per week data is entered for. there may be 300 rows of data for week 19 but only 4 dates,

 

[Tetra201]

Try

=SUM(IF(FREQUENCY(IF(($B$2:$B$999997=WEEKNUM($C1017))*($A$2:$A$999997=YEAR($C1017)),$C$2:$C$999997),$C$2:$C$999997),1))

 

[C_M2840]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YEAR
[/TD]
[TD]WEEK
[/TD]
[TD]DATE
[/TD]
[TD]NUMBER
[/TD]
[TD]X
[/TD]
[TD]X
[/TD]
[TD]X
[/TD]
[TD]X
[/TD]
[TD]X
[/TD]
[TD]X
[/TD]
[TD]X
[/TD]
[TD]X
[/TD]
[TD]X
[/TD]
[TD]X
[/TD]
[TD]=SUM(IF(FREQUENCY(IF($B$2:$B$999997=WEEKNUM($C940),$C$2:$C$999997),$C$2:$C$999997),1))
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017
[/TD]
[TD]24
[/TD]
[TD]13/6/17
[/TD]
[TD]46
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017
[/TD]
[TD]24
[/TD]
[TD]13/6/17
[/TD]
[TD]89
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017
[/TD]
[TD]24
[/TD]
[TD]13/6/17
[/TD]
[TD]90
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017
[/TD]
[TD]24
[/TD]
[TD]14/6/17
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017
[/TD]
[TD]24
[/TD]
[TD]14/6/17
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017
[/TD]
[TD]24
[/TD]
[TD]14/6/17
[/TD]
[TD]61
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017
[/TD]
[TD]24
[/TD]
[TD]14/6/17
[/TD]
[TD]23
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017
[/TD]
[TD]24
[/TD]
[TD]14/6/17
[/TD]
[TD]35
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017
[/TD]
[TD]24
[/TD]
[TD]15/6/17
[/TD]
[TD]87
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017
[/TD]
[TD]24
[/TD]
[TD]16/6/17
[/TD]
[TD]82
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018
[/TD]
[TD]24
[/TD]
[TD]12/6/18
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018
[/TD]
[TD]24
[/TD]
[TD]12/6/18
[/TD]
[TD]46
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018
[/TD]
[TD]24
[/TD]
[TD]12/6/18
[/TD]
[TD]98
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018
[/TD]
[TD]24
[/TD]
[TD]13/6/18
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018
[/TD]
[TD]24
[/TD]
[TD]13/6/18
[/TD]
[TD]15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018
[/TD]
[TD]24
[/TD]
[TD]14/6/18
[/TD]
[TD]84
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018
[/TD]
[TD]24
[/TD]
[TD]16/6/18
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]2018
[/TD]
[TD]24
[/TD]
[TD]16/6/
[/TD]
[TD]26
[/TD]

[/TR]
</tbody>[/TABLE]


Hey everyone:

here is an example of the table and the current formula I am using.

Unfortunately =SUM(IF(FREQUENCY(IF(($B$2:$B$999997=WEEKNUM($C1017))*($A$2:$A$999997=YEAR($C1017)),$C$2:$C$999997),$C$2:$C$999997),1)) came back with 197 as the answer

Hope the table above makes me easier to understand.

 

[Peter_SSs]

@C_M2840
There was very large blank space below your table & before your final comments. In removing that space I may have inadvertently removed some of your table. If that is the case & it has ruined the validity of your post, then you will have to post it again. Sorry about that.