Trying to calculate number of Contacts within a cell in another sheet

Options

A column in one sheet contains names using Contact List Column Type. Each cell may have a single contact or multiple contacts. Each row has various meeting information and this column lists who has attended the meeting. The other columns contain additional meeting information.

Trying to calculate how many meetings an individual contacts has attended. I.e How many times his/her name appears in the the column (i.e. attended meetings across multiple rows). I have created the following CONTAINS formula but it doesn't work, error message #INVALIDOPERATION

COUNTIF({Range}, CONTAINS([Column7]@row))

Range is the column in the source sheet that contains the name and [Column7] @ rows lists the name of the person I am trying to calculate the number of visits for. Column 7 is the same format as the source sheet (Contact List Type).

Any help much appreciated.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @mike.thorpe17421

    You're close! With a multi-select column I would suggest using HAS instead of CONTAINS. Inside of HAS (or CONTAINS) you'll need to list the range you're looking through. In this instance you already have the range listed, but you need the formula to check each cell, so we'll use @cell.

    Try:

    =COUNTIF({Range}, HAS(@cell, [Column7]@row))

    Cheers,

    Genevieve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!