Count multiple labels for one record once

[rhmkrmi]

Hello,

I have a large spreadsheet where I get multiple rows for the same person with repeated labels and I need to count the label only once for each person.
In this example below, I need to count 5 labels for John and 4 labels for Mary. At the moment, I get a total of 13 for each person which is incorrect.

Person	Label
John	Finance
John	Finance
John	Operations
John	Commercial
John	Commercial
John	Commercial
John	Commercial
John	Management
John	Management
John	Management
John	Procurement
John	Procurement
John	Procurement
Mary	Marketing
Mary	Marketing
Mary	Marketing
Mary	Marketing
Mary	Marketing
Mary	Operations
Mary	Operations
Mary	Operations
Mary	Operations
Mary	Management
Mary	Management
Mary	Procurement
Mary	Procurement

Is there a formula that I can use?

Thanks.

 

[kevin9999]

Perhaps this?

Book1
	A	B	C	D	E
1	Person	Label
2	John	Finance		John	5
3	John	Finance		Mary	4
4	John	Operations
5	John	Commercial
6	John	Commercial
7	John	Commercial
8	John	Commercial
9	John	Management
10	John	Management
11	John	Management
12	John	Procurement
13	John	Procurement
14	John	Procurement
15	Mary	Marketing
16	Mary	Marketing
17	Mary	Marketing
18	Mary	Marketing
19	Mary	Marketing
20	Mary	Operations
21	Mary	Operations
22	Mary	Operations
23	Mary	Operations
24	Mary	Management
25	Mary	Management
26	Mary	Procurement
27	Mary	Procurement
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUM(IF(FREQUENCY(IF($A$2:$A$27=D2,MATCH($B$2:$B$27,$B$2:$B$27,0)),ROW($B$2:$B$27)-ROW(B2)+1),1))
E3	E3	=SUM(IF(FREQUENCY(IF($A$2:$A$27=D3,MATCH($B$2:$B$27,$B$2:$B$27,0)),ROW($B$2:$B$27)-ROW(B2)+1),1))

 

[rhmkrmi]

Perhaps this?

Book1
	A	B	C	D	E
1	Person	Label
2	John	Finance		John	5
3	John	Finance		Mary	4
4	John	Operations
5	John	Commercial
6	John	Commercial
7	John	Commercial
8	John	Commercial
9	John	Management
10	John	Management
11	John	Management
12	John	Procurement
13	John	Procurement
14	John	Procurement
15	Mary	Marketing
16	Mary	Marketing
17	Mary	Marketing
18	Mary	Marketing
19	Mary	Marketing
20	Mary	Operations
21	Mary	Operations
22	Mary	Operations
23	Mary	Operations
24	Mary	Management
25	Mary	Management
26	Mary	Procurement
27	Mary	Procurement
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUM(IF(FREQUENCY(IF($A$2:$A$27=D2,MATCH($B$2:$B$27,$B$2:$B$27,0)),ROW($B$2:$B$27)-ROW(B2)+1),1))
E3	E3	=SUM(IF(FREQUENCY(IF($A$2:$A$27=D3,MATCH($B$2:$B$27,$B$2:$B$27,0)),ROW($B$2:$B$27)-ROW(B2)+1),1))

Thank you.

I actually need a formula in column C of your example above that can give me 0 or 1:

Person	Label	Count
John	Finance	1​
John	Commercial	1​
John	Operations	1​
John	Management	1​
John	Commercial	0​
John	Finance	0​
John	Commercial	0​
John	Procurement	1​
John	Commercial	0​
John	Management	0​
John	Procurement	0​
John	Procurement	0​
John	Finance	0​
Mary	Operations	1​
Mary	Operations	0​
Mary	Marketing	1​
Mary	Finance	1​
Mary	Marketing	0​
Mary	Operations	0​
Mary	Management	1​
Mary	Management	0​
Mary	Operations	0​
Mary	Management	0​
Mary	Management	0​
Mary	Procurement	1​
Mary	Management	0​

Thanks.

 

[kevin9999]

Book1
	A	B	C
1	Person	Label	Count
2	John	Finance	1
3	John	Commercial	1
4	John	Operations	1
5	John	Management	1
6	John	Commercial	0
7	John	Finance	0
8	John	Commercial	0
9	John	Procurement	1
10	John	Commercial	0
11	John	Management	0
12	John	Procurement	0
13	John	Procurement	0
14	John	Finance	0
15	Mary	Operations	1
16	Mary	Operations	0
17	Mary	Marketing	1
18	Mary	Finance	1
19	Mary	Marketing	0
20	Mary	Operations	0
21	Mary	Management	1
22	Mary	Management	0
23	Mary	Operations	0
24	Mary	Management	0
25	Mary	Management	0
26	Mary	Procurement	1
27	Mary	Management	0
Sheet3

Cell Formulas
Range	Formula
C2:C27	C2	=(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1)+0

 

[rhmkrmi]

Book1
	A	B	C
1	Person	Label	Count
2	John	Finance	1
3	John	Commercial	1
4	John	Operations	1
5	John	Management	1
6	John	Commercial	0
7	John	Finance	0
8	John	Commercial	0
9	John	Procurement	1
10	John	Commercial	0
11	John	Management	0
12	John	Procurement	0
13	John	Procurement	0
14	John	Finance	0
15	Mary	Operations	1
16	Mary	Operations	0
17	Mary	Marketing	1
18	Mary	Finance	1
19	Mary	Marketing	0
20	Mary	Operations	0
21	Mary	Management	1
22	Mary	Management	0
23	Mary	Operations	0
24	Mary	Management	0
25	Mary	Management	0
26	Mary	Procurement	1
27	Mary	Management	0
Sheet3

Cell Formulas
Range	Formula
C2:C27	C2	=(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1)+0

Excellent.
Thank you so much.
It is perfect.

 

[kevin9999]

Excellent.
Thank you so much.
It is perfect.

Glad I could help, and thanks for the feedback

 

[smozgur]

Excellent.
Thank you so much.
It is perfect.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.