Counting the number of employees in each project

i want to count the number of employees in each project using vlookup.my excel sheet details are huge ,i have given below sample ,can someone help ?

assume spreadsheet has details as below

employee project location
E1 P1 L1
E2 P2 L2 RESULT should be
E3 P1 L1 P1 3
E4 P2 L5 P2 3
E5 P1 L1
E6 P2 L3

try to follow instruction in top of page (yellow banner) to attach a sample worksheet with mockup data

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I will do it for you this time.)

Try this:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	employee	project	location
2	E1	P1	L1
3	E2	P2	L2	RESULT	should	be
4	E3	P1	L1	P1	3
5	E4	P2	L5	P2	3
6	E5	P1	L1
7	E6	P2	L3

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	E
4	=COUNTIF($B$2:$C$7,D4)
5	=COUNTIF($B$2:$C$7,D5)

Sheet: Sheet1

That's fine. Just put the list of projects wherever you want them.

May be

=COUNTIFS($B$2:$B$7,D4,$A$2:$A$7,$A$2:$A$7)

Originally Posted by selvirgo

i tried this ,but its not working...im getting the count as 0

I just fixed the range from B to A few seconds ago

Please manually mock-up what you want - attach your workbook.

In I11 copied down:

=COUNTIFS($A$2:$A$15,TRIM(H11),$B$2:$B$15,"<>")

TRIM is used because you have a trailing space in cell H13.

Your sample workbook was meant to show what you want, not something completely different, and you keep shifting the goalposts.

Try this in C2 copied down:

=COUNTIFS($A$2:$A$4000,A2,$B$2:$B$4000,"<>")

Excel 2016 (Windows) 32 bit

	A	B	C
1	Project Name	Emp Names
2	Project A	Emp 1	4
3	Project A	Emp 2	4
4	Project C	Emp 3	2
5	Project A	Emp 4	4
6	Project A	Emp 5	4
7	Project E	Emp 6	2
8	Project B	Emp 7	6
9	Project B	Emp 8	6
10	Project B	Emp 9	6
11	Project B	Emp 10	6
12	Project E	Emp 11	2
13	Project C	Emp 12	2
14	Project B	Emp 13	6
15	Project B	Emp 14	6

Sheet: Sheet1

Try this:

=IF(COUNTIF($A$2:$A2,A2)=1,COUNTIFS($A$2:$A$4000,A2,$B$2:$B$4000,"<>"),"")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Not if you have entered it correctly and copied it down:

Excel 2016 (Windows) 32 bit

	A	B	C
1	Project Name	Emp Names
2	Project A	Emp 1	4
3	Project A	Emp 2
4	Project C	Emp 3	2
5	Project A	Emp 4
6	Project A	Emp 5
7	Project E	Emp 6	2
8	Project B	Emp 7	6
9	Project B	Emp 8
10	Project B	Emp 9
11	Project B	Emp 10
12	Project E	Emp 11
13	Project C	Emp 12
14	Project B	Emp 13
15	Project B	Emp 14
16

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	C
2	=IF(COUNTIF($A$2:$A2,A2)=1,COUNTIFS($A$2:$A$4000,A2,$B$2:$B$4000,"<>"),"")

Sheet: Sheet1

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.