Count Unique Values with Multiple Criteria

[pdubq]

How do I count the unique Dates (Col B) for each project (Col A)? I'm expecting 6 for Project A and 2 for Project B (in the sample below).

Project	Sprint		Project	# of Sprints
A	9/17/2018		A	6
A	10/1/2018		B	2
A	10/1/2018
A	10/1/2018
A	10/15/2018
A	10/15/2018
A	10/29/2018
A	10/29/2018
A	10/29/2018
A	11/12/2018
A	11/12/2018
A	11/26/2018
A	11/26/2018
A	11/26/2018
A
A	12/10/2018
B	11/12/2018
B	11/12/2018
B	11/26/2018
B

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sandy666]

PivotTable with DataModel - DistinctCount

source			PivotTable
Project	Sprint		Project	Distinct Count of Sprint
A	17/09/2018​		A	8​
A	01/10/2018​		B	3​
A	01/10/2018​
A	01/10/2018​
A	15/10/2018​
A	15/10/2018​
A	29/10/2018​
A	29/10/2018​
A	29/10/2018​
A	12/11/2018​
A	12/11/2018​
A	26/11/2018​
A	26/11/2018​
A	26/11/2018​
A
A	10/12/2018​
B	12/11/2018​
B	12/11/2018​
B	26/11/2018​
B

 

[strooman]

C2: =SUM(IF(FREQUENCY(IF($B$2:$B$21<>"";IF($A$2:$A$21=A2;MATCH("~"&$B$2:$B$21;$B$2:$B$21&"";0)));ROW($B$2:$B$21)-ROW($B$2)+1);1))
And drag down

Or

F2: =SUM(IF(FREQUENCY(IF($B$2:$B$21<>"";IF($A$2:$A$21=E2;MATCH("~"&$B$2:$B$21;$B$2:$B$21&"";0)));ROW($B$2:$B$21)-ROW($B$2)+1);1))
And drag down

Insert formula with: Ctrl+Shift+Enter

Row\Col	A​	B​	C​	D​	E​	F​
1​	Project	Sprint	Count		Project	Count
2​	A	17-9-2018​	7​		A	7​
3​	A	1-10-2018​	7​		B	2​
4​	A	1-10-2018​	7​
5​	A	1-10-2018​	7​
6​	A	15-10-2018​	7​
7​	A	15-10-2018​	7​
8​	A	29-10-2018​	7​
9​	A	29-10-2018​	7​
10​	A	29-10-2018​	7​
11​	A	12-11-2018​	7​
12​	A	12-11-2018​	7​
13​	A	26-11-2018​	7​
14​	A	26-11-2018​	7​
15​	A	26-11-2018​	7​
16​	A		7​
17​	A	10-12-2018​	7​
18​	B	12-11-2018​	2​
19​	B	12-11-2018​	2​
20​	B	26-11-2018​	2​
21​	B		2​

 

[pdubq]

I receive the following error message when using the formula in F2...

There's a problem with this formula:
Not trying to type a formula?
When the first character is equal (=) or minus (-) sign, Excel thinks it's a formula:
- you type: =1+1, cell shows 2
To get around this, type an apostrophe (') first:
- you type: '=1+1, cell shows: =1+1

I confirmed that I have the (=) and entered with Ctrl+Shift+Enter. The Formula is highlighting the "" in the first IF statement.

....

F2: =SUM(IF(FREQUENCY(IF($B$2:$B$21<>"";IF($A$2:$A$21=E2;MATCH("~"&$B$2:$B$21;$B$2:$B$21&"";0)));ROW($B$2:$B$21)-ROW($B$2)+1);1))
And drag down

Insert formula with: Ctrl+Shift+Enter

Row\Col	A​	B​	C​	D​	E​	F​
1​	Project	Sprint	Count		Project	Count
2​	A	17-9-2018​	7​		A	7​
3​	A	1-10-2018​	7​		B	2​
4​	A	1-10-2018​	7​
5​	A	1-10-2018​	7​
6​	A	15-10-2018​	7​
7​	A	15-10-2018​	7​
8​	A	29-10-2018​	7​
9​	A	29-10-2018​	7​
10​	A	29-10-2018​	7​
11​	A	12-11-2018​	7​
12​	A	12-11-2018​	7​
13​	A	26-11-2018​	7​
14​	A	26-11-2018​	7​
15​	A	26-11-2018​	7​
16​	A		7​
17​	A	10-12-2018​	7​
18​	B	12-11-2018​	2​
19​	B	12-11-2018​	2​
20​	B	26-11-2018​	2​
21​	B		2​

<tbody>
</tbody>

[/QUOTE]

 

[strooman]

Did you replace the ; (semi-colon) with a comma? This has to do with regional settings. In The Netherlands we use ; (semi-colon) as delimiter in formula's.