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?

	A​	B​	C​	D​	E​	F​	G​
1​	Year	Week	Date		Year	Week	Count
2​	2018​	19​	5/9/2018​		2018​	19​	3​
3​	2018​	26​	6/28/2018​		2017​	49​	2​
4​	2018​	26​	6/30/2018​
5​	2018​	19​	5/12/2018​
6​	2018​	21​	5/26/2018​
7​	2018​	19​	5/8/2018​
8​	2017​	49​	12/3/2017​
9​	2017​	15​	4/12/2017​
10​	2017​	35​	8/29/2017​
11​	2017​	49​	12/4/2017​
12​	2017​	23​	6/7/2017​
13​	2017​	32​	8/9/2017​
14​	2017​	38​	9/19/2017​

Sheet: Sheet19

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]

A	B	C	D	E	F	J	K	L	M	N	O	P	R	S
YEAR	WEEK	DATE	NUMBER	X	X	X	X	X	X	X	X	X	X	=SUM(IF(FREQUENCY(IF($B$2:$B$999997=WEEKNUM($C940),$C$2:$C$999997),$C$2:$C$999997),1))
2017	24	13/6/17	46
2017	24	13/6/17	89
2017	24	13/6/17	90
2017	24	14/6/17	2
2017	24	14/6/17	5
2017	24	14/6/17	61
2017	24	14/6/17	23
2017	24	14/6/17	35
2017	24	15/6/17	87
2017	24	16/6/17	82
2018	24	12/6/18	1
2018	24	12/6/18	46
2018	24	12/6/18	98
2018	24	13/6/18	12
2018	24	13/6/18	15
2018	24	14/6/18	84
2018	24	16/6/18	22
2018	24	16/6/	26

<tbody>
</tbody>

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.