+ Reply to Thread
Results 1 to 22 of 22

Counting the number of employees in each project

  1. #1
    Registered User
    Join Date
    07-01-2020
    Location
    india
    MS-Off Ver
    1908
    Posts
    8

    Question 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
    Last edited by AliGW; 07-01-2020 at 04:07 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,439

    Re: counting using vlookup

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

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,011

    Re: counting using vlookup

    Your title should change as it does not represent the problem. Something like How to count the number of employees in each project

    The solution uses countif rather than vlookup, so you should not specify the method in the question. vlookup does not count!

    Your other option is a pivot table which may be better and quicker

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: counting using vlookup

    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.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Counting the number of employees in each project

    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

  6. #6
    Registered User
    Join Date
    07-01-2020
    Location
    india
    MS-Off Ver
    1908
    Posts
    8

    Re: Counting the number of employees in each project

    ALIGW - Thanks for the immediate response

    i gave the below sample as example - the result need not be in cell 4 & 5 ..its just to show what is the output in no's i need..

    for each project i need the count of employees tagged to it..

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Counting the number of employees in each project

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

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,439

    Re: Counting the number of employees in each project

    May be

    =COUNTIFS($B$2:$B$7,D4,$A$2:$A$7,$A$2:$A$7)
    Last edited by bebo021999; 07-01-2020 at 05:28 AM. Reason: Range typo

  9. #9
    Registered User
    Join Date
    07-01-2020
    Location
    india
    MS-Off Ver
    1908
    Posts
    8

    Re: Counting the number of employees in each project

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

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,439

    Re: Counting the number of employees in each project

    Quote Originally Posted by selvirgo View Post
    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

  11. #11
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,011

    Re: Counting the number of employees in each project

    Or if it is hard keeping an up to date list of projects a pivot table may be a better option

    https://exceljet.net/pivot-table/piv...le-basic-count

  12. #12
    Registered User
    Join Date
    07-01-2020
    Location
    india
    MS-Off Ver
    1908
    Posts
    8

    Re: Counting the number of employees in each project

    Bebo and AliGW - thanks for the response - i tried both of your solutions,its not working for me..u can ignore the location col..just the project and employee columns can be taken into account for the counts..pls help

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Counting the number of employees in each project

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

  14. #14
    Registered User
    Join Date
    07-01-2020
    Location
    india
    MS-Off Ver
    1908
    Posts
    8

    Re: Counting the number of employees in each project

    attaching the worksheet
    Attached Files Attached Files

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Counting the number of employees in each project

    In I11 copied down:

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

  16. #16
    Registered User
    Join Date
    07-01-2020
    Location
    india
    MS-Off Ver
    1908
    Posts
    8

    Re: Counting the number of employees in each project

    i am not sure why you have used TRIM here..i said im manually putting the results for people to understand..results need not be on I11..i want the results on col C against all rows..again the above sol didint work out in my actual data sheet with 3000+ rows

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Counting the number of employees in each project

    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
    Last edited by AliGW; 07-01-2020 at 06:39 AM. Reason: Typo corrected.

  18. #18
    Registered User
    Join Date
    07-01-2020
    Location
    india
    MS-Off Ver
    1908
    Posts
    8

    Re: Counting the number of employees in each project

    yes it worked ,thanks a lot - can we do a small tweak here,the count should not repeat on every row,say suppose if it displays 4 for project A ,it shouldn't display again in another cell with project A...count should be displayed once for each project..is that possible ?

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Counting the number of employees in each project

    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.

  20. #20
    Registered User
    Join Date
    07-01-2020
    Location
    india
    MS-Off Ver
    1908
    Posts
    8

    Re: Counting the number of employees in each project

    it is still displaying counts on every row

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Counting the number of employees in each project

    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
    Attached Files Attached Files
    Last edited by AliGW; 07-01-2020 at 06:55 AM.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Counting the number of employees in each project

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Counting text that appear less/more than another, using VLOOKUP
    By Jones90 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-11-2017, 12:33 PM
  2. VLOOKUP - Counting Instances
    By bdav1216 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-04-2017, 10:17 AM
  3. [SOLVED] Counting certain values using a vlookup.
    By CallumWebley in forum Excel General
    Replies: 3
    Last Post: 12-17-2015, 09:01 AM
  4. [SOLVED] vlookup with counting? does this need to be done with Arrays?
    By StarFyre in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-15-2014, 03:43 PM
  5. [SOLVED] VLOOKUP & COUNTING AND SUMIFS in a DataSet
    By Jason Carlos in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-27-2014, 08:14 PM
  6. [SOLVED] Counting cells that are found in a VLOOKUP
    By mnowinski in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-14-2014, 11:09 AM
  7. Excel 2007 : Vlookup with a counting feature?
    By budchevy in forum Excel General
    Replies: 2
    Last Post: 05-09-2011, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1