COUNTIF Formula for Contact Column

mel.greenspanmel.greenspan ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
06/25/19 Edited 12/09/19

I have several tasks set up for team members using their email addresses in the Contact column. Some tasks have 1 member assigned and some have multiple. I have to run each person's workload and am trying to use a separate sheet that counts the number of times their email address is in the Contact column. However, the COUNTIF formula only counts them if there is only 1 person assigned in the Contact column. If there is more than 1 email address, the formula counts it as "0", even if their email address is in the cell. The goal is to have the workload calculate in "real time" in this sheet, using the COUNTIF formula, and be linked to the master sheet where the source data is located.

 

I've already submitted a product enhancement request, but need a workaround in the interim! Any ideas would be appreciated. 

 

 

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Mel,

    I think I have something that will help. I'll get back to this post a little later. 

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You should be able to incorporate the FIND function into the criteria of your COUNTIFS instead of looking for a match.

     

    If your current formula looks like this which counts only if the email is the only one in the cell:

     

    =COUNTIFS({Email Address Column}, "[email protected]")

     

    then you would want to try something along the lines of this:

     

    =COUNTIFS({Email Address Column}, FIND("[email protected]", @cell) > 0)

    thinkspi.com

  • mel.greenspanmel.greenspan ✭✭✭✭✭

    This doesn't seem to work when I'm referencing a Contact column in another sheet? It does work when I reference a range within the same sheet I'm doing the countifs in. Any ideas?  

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    What is your current formula?

    thinkspi.com

  • mel.greenspanmel.greenspan ✭✭✭✭✭

    =COUNTIFS({Contact Column}, FIND("[email protected]", @cell) > 0)

    Also tried this:

    =COUNTIFS({Contact Column}, FIND("Person Name", @cell) > 0)

     

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    And you are following the correct steps to select the appropriate range for your cross sheet reference?

     

    Are you typing in the correct email address? Spelling, punctuation, and upper/lower case all have to match.

     

    It is working for me.

    thinkspi.com

  • mel.greenspanmel.greenspan ✭✭✭✭✭

    WORKING NOW!

    Thanks!!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Excellent! Happy to help! yes

    thinkspi.com

  • How do you make this work if you have other criteria that you want to be present, in addition to the "find" criteria. I'm trying to only count if all 4 of the criteria are present, (issue=project, progress = not full, status = yes and a particular person is in the Leader column) but it seems like it's not taking into account all the criteria, but I can't figure out how it's getting the 82 to figure out my error.

    Here's my formula:

    =COUNTIFS({Issue Type}, "Project", {Progress}, "<> Full", {Status}, "Yes", {Leader Responsible Party}, (FIND("Person 1", @cell) > 0))

    I'm expecting 21 (hand count) and it's showing 82. I would expect 130 if I as looking for a full status.

    Any suggestions?

  • Hi @ialarson

    There are a few syntax elements here to adjust. The quotes for your second criteria should only be around "Full" and not the <> symbol. I would also personally use the HAS function to search for a person within the contact column if it's multi-select, like so:


    =COUNTIFS({Issue Type}, "Project", {Progress}, <> "Full", {Status}, "Yes", {Leader Responsible Party}, HAS(@cell, "Person 1"))


    Let me know if this returns the correct result!

Sign In or Register to comment.