Unique record count with multiple criteria

[praveenj]

Hi

This is me first time posting on this forum, so hoping someone can answer.

I have data in the below sample format. I am trying to get a record count when the values in column 1(Week) and column2 (Name) match together. For example, User1 has three entries for 7/14/2018. So in column3, I would expect to see 3 in the first row. However, I am getting 1 using the below formula. What do I need to do to fix it?

=SUM(--(FREQUENCY(IF($B$2:$B$10=[@Name], IF($A$2:$A$10=[@Week], MATCH($A$2:$A$10, $A$2:$A$10, 0))), ROW($A$2:$A$10)-ROW(A2)+1)>0))

Week Name Count
7/14/2018 User1 1
7/28/2018 User1 1
6/30/2018 User1 1
7/21/2018 User2 1
7/7/2018 User2 1
7/14/2018 User1 1
7/14/2018 User1 1
7/14/2018 User2 1
7/7/2018 User1 1

Thanks.

 

[AhoyNC]

Is this what you want?

Excel Workbook
	A	B	C
1	Week	Name	Count
2	7/14/2018	User1	3
3	7/28/2018	User1	1
4	6/30/2018	User1	1
5	7/21/2018	User2	1
6	7/7/2018	User2	1
7	7/14/2018	User1	3
8	7/14/2018	User1	3
9	7/14/2018	User2	1
10	7/7/2018	User1	1
Sheet

 

[Marcelo Branco]

Or

=COUNTIFS([Name],[@Name],[Week],[@Week])

M.

 

[sandy666]

or

#"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Week", "Name"}, {{"Count", each Table.RowCount(_), type number}})

 

[praveenj]

Thank you all for the suggestions. They work. What if I want to get a unique count of the weeks associated with User1? In the above example, it should be 4 (6/30, 7/7, 7/14, 7/28)? Thanks.

 

[Aladin Akyurek]

Thank you all for the suggestions. They work. What if I want to get a unique count of the weeks associated with User1? In the above example, it should be 4 (6/30, 7/7, 7/14, 7/28)? Thanks.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$10="User1",$A$2:$A$10),$A$2:$A$10),1))

 

[AhoyNC]

This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F
1	Week	Name	Count		Week Count for	User1
2	7/14/2018	User1	3			4
3	7/28/2018	User1	1
4	6/30/2018	User1	1
5	7/21/2018	User2	1
6	7/7/2018	User2	1
7	7/14/2018	User1	3
8	7/14/2018	User1	3
9	7/14/2018	User2	1
10	7/7/2018	User1	1
Sheet

 

[praveenj]

Thank you, @AhoyNC.

 

[sandy666]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Week[/td][td=bgcolor:#5B9BD5]Name[/td][td][/td][td=bgcolor:#70AD47]Week[/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Count[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]7/14/2018[/td][td=bgcolor:#DDEBF7]User1[/td][td][/td][td=bgcolor:#E2EFDA]

14/07/2018​

[/td][td=bgcolor:#E2EFDA]User1[/td][td=bgcolor:#E2EFDA]

3​

[/td][td][/td][td=bgcolor:#E2EFDA]User1[/td][td=bgcolor:#E2EFDA]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7/28/2018[/td][td]User1[/td][td][/td][td]

28/07/2018​

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

1​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]6/30/2018[/td][td=bgcolor:#DDEBF7]User1[/td][td][/td][td=bgcolor:#E2EFDA]

30/06/2018​

[/td][td=bgcolor:#E2EFDA]User1[/td][td=bgcolor:#E2EFDA]

1​

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

[tr=bgcolor:#FFFFFF][td]7/21/2018[/td][td]User2[/td][td][/td][td]

21/07/2018​

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

1​

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

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

07/07/2018​

[/td][td=bgcolor:#DDEBF7]User2[/td][td][/td][td=bgcolor:#E2EFDA]

07/07/2018​

[/td][td=bgcolor:#E2EFDA]User2[/td][td=bgcolor:#E2EFDA]

1​

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

[tr=bgcolor:#FFFFFF][td]7/14/2018[/td][td]User1[/td][td][/td][td]

14/07/2018​

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

1​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]7/14/2018[/td][td=bgcolor:#DDEBF7]User1[/td][td][/td][td=bgcolor:#E2EFDA]

07/07/2018​

[/td][td=bgcolor:#E2EFDA]User1[/td][td=bgcolor:#E2EFDA]

1​

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

[tr=bgcolor:#FFFFFF][td]7/14/2018[/td][td]User2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

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

07/07/2018​

[/td][td=bgcolor:#DDEBF7]User1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

M code for the 2nd result

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Week", type date}}, "en-US"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Week", "Name"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Name"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows1", each ([Name] = "User1"))
in
    #"Filtered Rows"[/SIZE]