Count Unique Values with Multiple Criteria

P

pdubq

New Member
Joined
Mar 26, 2014
Messages
2
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).

[TABLE="width: 454"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Project[/TD]
[TD]Sprint[/TD]
[TD][/TD]
[TD]Project[/TD]
[TD]# of Sprints[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9/17/2018[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10/1/2018[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10/1/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10/1/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10/15/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10/15/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10/29/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10/29/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10/29/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]11/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]11/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]11/26/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]11/26/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]11/26/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]12/10/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]11/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]11/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]11/26/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
PivotTable with DataModel - DistinctCount

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]source[/td][td][/td][td][/td][td]PivotTable[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Project[/td][td]Sprint[/td][td][/td][td=bgcolor:#DDEBF7]Project[/td][td=bgcolor:#DDEBF7]Distinct Count of Sprint[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
17/09/2018​
[/td][td][/td][td]A[/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
01/10/2018​
[/td][td][/td][td]B[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
01/10/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
01/10/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
15/10/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
15/10/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
29/10/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
29/10/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
29/10/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
12/11/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
12/11/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
26/11/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
26/11/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
26/11/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
10/12/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]
12/11/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]
12/11/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]
26/11/2018​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
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


[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr][tr][td]
1​
[/td][td="bgcolor:#0070C0"]Project[/td][td="bgcolor:#0070C0"]Sprint[/td][td="bgcolor:#0070C0"]Count[/td][td][/td][td="bgcolor:#0070C0"]Project[/td][td="bgcolor:#0070C0"]Count[/td][/tr]
[tr][td]
2​
[/td][td]A[/td][td]
17-9-2018​
[/td][td]
7​
[/td][td][/td][td]A[/td][td]
7​
[/td][/tr]
[tr][td]
3​
[/td][td]A[/td][td]
1-10-2018​
[/td][td]
7​
[/td][td][/td][td]B[/td][td]
2​
[/td][/tr]
[tr][td]
4​
[/td][td]A[/td][td]
1-10-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
5​
[/td][td]A[/td][td]
1-10-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
6​
[/td][td]A[/td][td]
15-10-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
7​
[/td][td]A[/td][td]
15-10-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
8​
[/td][td]A[/td][td]
29-10-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td]A[/td][td]
29-10-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td]A[/td][td]
29-10-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
11​
[/td][td]A[/td][td]
12-11-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
12​
[/td][td]A[/td][td]
12-11-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
13​
[/td][td]A[/td][td]
26-11-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
14​
[/td][td]A[/td][td]
26-11-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
15​
[/td][td]A[/td][td]
26-11-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
16​
[/td][td]A[/td][td][/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
17​
[/td][td]A[/td][td]
10-12-2018​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
18​
[/td][td]B[/td][td]
12-11-2018​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
19​
[/td][td]B[/td][td]
12-11-2018​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
20​
[/td][td]B[/td][td]
26-11-2018​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
21​
[/td][td]B[/td][td][/td][td]
2​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
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


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD="bgcolor: #0070C0"]Project[/TD]
[TD="bgcolor: #0070C0"]Sprint[/TD]
[TD="bgcolor: #0070C0"]Count[/TD]
[TD][/TD]
[TD="bgcolor: #0070C0"]Project[/TD]
[TD="bgcolor: #0070C0"]Count[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]A[/TD]
[TD]
17-9-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]
7​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]A[/TD]
[TD]
1-10-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]A[/TD]
[TD]
1-10-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]A[/TD]
[TD]
1-10-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]A[/TD]
[TD]
15-10-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]A[/TD]
[TD]
15-10-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]A[/TD]
[TD]
29-10-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]A[/TD]
[TD]
29-10-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]A[/TD]
[TD]
29-10-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]A[/TD]
[TD]
12-11-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]A[/TD]
[TD]
12-11-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]A[/TD]
[TD]
26-11-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]A[/TD]
[TD]
26-11-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]A[/TD]
[TD]
26-11-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD]A[/TD]
[TD]
10-12-2018​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
18​
[/TD]
[TD]B[/TD]
[TD]
12-11-2018​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
19​
[/TD]
[TD]B[/TD]
[TD]
12-11-2018​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
20​
[/TD]
[TD]B[/TD]
[TD]
26-11-2018​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
21​
[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/QUOTE]
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Pulling Correct Number of Dates Based on Column Value
Replies
1
Views
77
dreid1011
dreid1011
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
530
etaf
etaf
S
  • Question Question
Formatting Midnight in a date to 24 instead of 0:00
Replies
4
Views
699
shepcs
S
L
  • Question Question
INDEX MATCH
Replies
1
Views
350
Fluff
Fluff
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
637
mrshuck
M

Forum statistics

Threads
1,225,754
Messages
6,186,825
Members
453,377
Latest member
JoyousOne

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top