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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
494
etaf
etaf
S
  • Question Question
Formatting Midnight in a date to 24 instead of 0:00
Replies
4
Views
527
shepcs
S
L
  • Question Question
INDEX MATCH
Replies
1
Views
322
Fluff
Fluff
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
538
mrshuck
M
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
567
Domenic
D

Forum statistics

Threads
1,223,054
Messages
6,169,834
Members
452,284
Latest member
TKM623

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