Counting Formula Help
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!
Best Answer
-
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.
Answers
-
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?
-
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.
-
@Paul Newcome Thank you so much for your help Paul!! I previously misplaced a parenthesis. This formula works perfectly!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!