Counting Formula Help

malmcfall

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!

Paul Newcome

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.

Paul Newcome

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

malmcfall

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

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?

malmcfall

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

heyjay

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

malmcfall

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.

Paul Newcome

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.

malmcfall

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