# Counting Formula Help

edited 03/18/24

Hi,

I am looking for a formula that counts that instances that a particular department was listed. In my scenario, Person 1 & Person 2 belong to the same department. Current formula I am using is Attendances by Person 1 + Attendances by Person 2 which gives me a result of 6 however, since they are from the same department, the result needs to reflect 5 since the department attended 5 times. Attendees is a multi contact column.

Thank you so much!

Tags:

• ✭✭✭✭✭✭

You'll need a JOIN/COLLECT

=JOIN(COLLECT({Reference Sheet Department Column}, {Reference Sheet Contact Column}, HAS(Attendees@row, @cell)), CHAR(10))

Just make sure the attendees column is set to allow multiple entries.

• ✭✭✭✭✭✭

What are you using to indicate which department each attendee is from?

• I decided that it might be easier for me to denote that in a separate column provided by an automated formula and working on that formula rather than the original one that I inquired about. Is it possible to have a formula populate multiple items for a drop down multi select? @Paul Newcome

• ✭✭✭✭✭✭

It is possible, but we still need to know how you are designating Person 1 to Department 1. Do you have a reference list of everyone in one column and their department in another?

• edited 03/19/24

I have a separate sheet with all employee information, here is my example where Contact is a contact column property and Department is text num. @Paul Newcome

• ✭✭✭✭✭
```=COUNTIF(
[Department]:[Department],
HAS(@cell, "Department 1")
)
```

...

• Thank you! The count formula works however, I am needing the department(s) to auto populate based on attendees and am working on a formula for that.

• ✭✭✭✭✭✭

You'll need a JOIN/COLLECT

=JOIN(COLLECT({Reference Sheet Department Column}, {Reference Sheet Contact Column}, HAS(Attendees@row, @cell)), CHAR(10))

Just make sure the attendees column is set to allow multiple entries.

• edited 03/19/24

@Paul Newcome Thank you so much for your help Paul!! I previously misplaced a parenthesis. This formula works perfectly!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!