Forum Discussion

Gidget2324's avatar
Gidget2324
Copper Contributor
May 01, 2024

Is there a way to count items from the second sheet on my 1st sheet?

Hi! I'm a recruiter for a staffing firm and we have a new client tracker that is comprised of two sheets. The first sheet has the recruiter's name and their total clients and then the second sheet lists each individual client name in each single cell. I'm curious if there is a formula I can enter in the cells on sheet one by each recruiters name that will be able to automatically add the total amount of clients listed in sheet two. screen clips for reference:


Sheet 1 - looking to add 'formula' to the yellow highlighted column -

 

 

Sheet 2 - list of recruiters with their associated client names -

 

 

Is anyone able to help me in figuring out how to make this more autonomous so that sheet 1 numbers will automatically update when I add a new client on sheet 2?

 

And while I'm at it, is there a way to automate the 'rank' column so based on the information in the yellow highlighted area on sheet 1 it will automatically rank each recruiter based on their total client count from page 2? 

 

Thank you! 

excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    May 02, 2024
    It is showing same name for recruiters? How you will differentiate which recruiter has how many clients? At least give them name as recruiter1, recruiter2 ..... etc.
    • Gidget2324's avatar
      Gidget2324
      Copper Contributor
      May 02, 2024

      Harun24HR I only listed them as the same name for the purposes of keeping our recruiter's names confidential. 🙂 I didn't want to blast my team's names all over the internet. On our actual spreadsheet there is each of their names, their sales hire date, their cost center number and then the number of clients they have based on the information entered into sheet two. 

      • Harun24HR's avatar
        Harun24HR
        Bronze Contributor
        May 03, 2024
        Then use fake names like Name1, Name2, Name3.
  • djclements's avatar
    djclements
    Bronze Contributor
    May 03, 2024

    Gidget2324 Assuming "ACCOUNT EXECUTIVE" is in column B and "TOTAL" is in column E, try the following formula in cell E2, then copy down as needed:

     

    =COUNTA(XLOOKUP(B2, Sheet2!$1:$1, Sheet2!$2:$1000, Sheet2!$XFD$1048576))

     

    Note: the reference to Sheet2!$XFD$1048576 in the [if_not_found] argument is used to ensure COUNTA returns 0 instead of 1 when XLOOKUP does not find a match.

     

    Alternatively, I would recommend setting up the client list in a normalized table, so it's easier to keep track of other information too. For example, the client table could have columns for Name, Address, Phone, Email, Date Recruited, etc., as well as a column with a data validation list to select the "ACCOUNT EXECUTIVE". When using structured Excel tables (Insert > Table), the formula to count the number of clients assigned to each executive could be:

     

    =COUNTIF(tblClients[ACCOUNT EXECUTIVE], [@[ACCOUNT EXECUTIVE]])

     

    Note: table names default to "Table1", "Table2", etc. when they are first created, but can be changed by going to the Table Design tab on the Ribbon.

     

    See the attached sample workbook, which contains a separate client sheet for the two methods shown above...

Resources