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

mike.thorpe17421

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.

Genevieve P.

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

Genevieve P.

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

mike.thorpe17421

Brilliant, thanks Genevieve, that works 😀

Have a great weekend.