Count common value

[Boivin12]

I have a list of people and a list of team number.
Every person is attached to one singe team but its name appear several times (I made a simple table).
I would like to know how many people there are in a team (3 in team 1 and 1 in team 2 in my example).
I know it's not a big deal but I can't wrap my head around it...

Person ID	Team number
A	1
A	1
A	1
B	2
B	2
C	1
C	1
C	1
D	1

 

[eiloken]

I have a list of people and a list of team number.
Every person is attached to one singe team but its name appear several times (I made a simple table).
I would like to know how many people there are in a team (3 in team 1 and 1 in team 2 in my example).
I know it's not a big deal but I can't wrap my head around it...

Person ID	Team number
A	1
A	1
A	1
B	2
B	2
C	1
C	1
C	1
D	1

you can use "Remove Duplicates" with column "Person ID" and "Team number" then used countif in "Team number" to do it

 

[RobP]

or maybe this could help : by entering your team number in E1, result appears in D2.

Book1
	A	B	C	D	E
1	Person ID	Team number		Team:	2
2	A	1		2
3	A	1
4	A	1
5	B	2
6	B	2
7	C	1
8	C	1
9	C	1
10	D	4
11	E	2
12	E	2
13
Sheet1

Cell Formulas
Range	Formula
D2	D2	=LET(names,UNIQUE(A2:B12),numbers,CHOOSECOLS(names,2),SUMPRODUCT(--(numbers=E1)))

 

[Boivin12]

I should have notified sorry. But, I can't delete duplicate value ( I have to keep my data for other functions) and I would like to have the appearing on a third column, for each row.

A	1	3
A	1	3
A	1	3
B	2	1
B	2	1
C	1	3
C	1	3
D	1	3

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1
2	A	1	3
3	A	1	3
4	A	1	3
5	B	2	1
6	B	2	1
7	C	1	3
8	C	1	3
9	D	1	3
Sheet6

Cell Formulas
Range	Formula
C2:C9	C2	=MAP(B2:B9,LAMBDA(m,ROWS(UNIQUE(FILTER(A2:A100,B2:B100=m)))))
Dynamic array formulas.

 

[Boivin12]

How about

Fluff.xlsm
	A	B	C
1
2	A	1	3
3	A	1	3
4	A	1	3
5	B	2	1
6	B	2	1
7	C	1	3
8	C	1	3
9	D	1	3
Sheet6

Cell Formulas
Range	Formula
C2:C9	C2	=MAP(B2:B9,LAMBDA(m,ROWS(UNIQUE(FILTER(A2:A100,B2:B100=m)))))
Dynamic array formulas.

It gives me a #SPILL error

 

[Fluff]

Clear all cells in col C & then re-enter the formula in C2 only.

 

[RobP]

did you try my formula in D2 ?

Cell Formulas
Range	Formula
D2	D2	=LET(names,UNIQUE(A2:B12),numbers,CHOOSECOLS(names,2),SUMPRODUCT(--(numbers=E1))

 

[Boivin12]

did you try my formula in D2 ?

Cell Formulas
Range	Formula
D2	D2	=LET(names,UNIQUE(A2:B12),numbers,CHOOSECOLS(names,2),SUMPRODUCT(--(numbers=E1))

I need to automatically see the result next to each cell. I can't enter one value after another (I have over 600 teams and 250,000 rows).

 

[RobP]

sorry, I missed your comment ... clear. then Fluffs should do you well.

cheers
Rob