Counting unique values in range based on multiple criteria

[SteveNL86]

Dear reader,

I am trying to count the unique values in a column, based on 1 week, 2 weeks and 3 weeks.

So I've done column C with an index/match formula, giving me unique weeknumbers ranging from 1 to 52.
I'm struggling with working out a working formula for column D, E & F.

In column D I'm just trying to get the total amoun to unique values for each week.
In column E I'm trying to get the same result but then for that week and the next week. So the results in E2 would be the total unique values for week 1 and 2.
In column F the same results but then for week 1, 2 & 3. F3 would give the results for week 2, 3 & 4.

Example:

Value	Week	Unique Week Numbers	Total Unique Values Per Week	Total Unique Values Per 2 Weeks	Total Unique Value Per 3 Weeks
10	1	1	3	3	4
15	1	2	3	4	4
20	1	3	2	3
15	2	4	1
20	2
10	2
30	3
10	3
20	4

Any help is much appreciated!

 

[AhoyNC]

Maybe:
Copy formulas in D2, E2 and F2 down the rows.

Book1
	A	B	C	D	E	F
1	Value	Week	Unique Week Numbers	Total Unique Values Per Week	Total Unique Values Per 2 Weeks	Total Unique Value Per 3 Weeks
2	10	1	1	3	3	4
3	15	1	2	3	4	4
4	20	1	3	2	3
5	15	2	4	1
6	20	2
7	10	2
8	30	3
9	10	3
10	20	4
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=SUM(IF(FREQUENCY(IF($B$2:$B$10>=C2,IF($B$2:$B$10<=C4,MATCH($A$2:$A$10,$A$2:$A$10,0))),ROW($A$2:$A$10)-ROW($A$2)+1),1))
E2:E4	E2	=SUM(IF(FREQUENCY(IF($B$2:$B$10>=C2,IF($B$2:$B$10<=C3,MATCH($A$2:$A$10,$A$2:$A$10,0))),ROW($A$2:$A$10)-ROW($A$2)+1),1))
D2:D5	D2	=SUM(IF(FREQUENCY(IF($B$2:$B$10=C2,MATCH($A$2:$A$10,$A$2:$A$10,0)),ROW($A$2:$A$10)-ROW($A$2)+1),1))

 

[SteveNL86]

Thanks for the help AhoyNC but the formula don't seem to work for me. I only get a '1' a result in D2, E2, F2, and when dragging it down it only gives me #value errors unfortunately.

I even tried replicating exactly what you posted but it doesn't seem to give me the same results.

 

[Fluff]

You will need to confirm those formulae with Ctrl Shift Enter & then drag down.

 

[SteveNL86]

Ah yes, ofcourse. My bad! It seems to work, thanks for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.