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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
41
dreid1011
dreid1011
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
499
etaf
etaf
S
  • Question Question
Formatting Midnight in a date to 24 instead of 0:00
Replies
4
Views
559
shepcs
S
L
  • Question Question
INDEX MATCH
Replies
1
Views
334
Fluff
Fluff
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
552
mrshuck
M

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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