SUMIFS I think?
I am trying to count a total number of employees in a multi-select drop down and also only count those employees if they attended meetings in November, December, January, Etc. All of this data is absorbed via a form.
My current formula is:
=SUMIFS([Employees at meeting]:[Employees at meeting], >1, Date:Date, AND(@cell >= DATE(2021, 11, 1), @cell <= DATE(2021, 11, 31)))
If I run a COUNTIFS, my returned amount is "1", showing their is a submission, but not counting the number of selections in the multi-select cell:
=COUNTIFS([Employees at meeting]:[Employees at meeting], >1, Date:Date, AND(@cell >= DATE(2021, 11, 1), @cell <= DATE(2021, 11, 31)))
I am trying to produce this in a sheet summary on the right hand side.
What am I missing here?
Kyle
Best Answer
-
Yeah. You are going to want a COUNTM/COLLECT combo.
=COUNTM(COLLECT([Employees At Meeting]:[Employees At Meeting], Date:Date, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = 2021))
Answers
-
Are you able to provide a screenshot for reference? It almost sounds like what you are going to need is a COUNTM/COLLECT combo.
-
Both categories I am looking to quantify are shown. Employees at meeting is the multi select, Date is meant to be used for a monthly count of employees total, the number of meetings is not as important.
-
Yeah. You are going to want a COUNTM/COLLECT combo.
=COUNTM(COLLECT([Employees At Meeting]:[Employees At Meeting], Date:Date, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = 2021))
-
Paul,
Woah, I need to get on your level.
Great work man!!
-
Happy to help. 👍️
You can think of the COLLECT function as adding an "IFS" to any other function. Want to join certain cells but only if they meet certain criteria? We don't have a JOINIFS, but we do have JOIN/COLLECT. Same for here. You wanted to count the number of entries in a series of multi-select cells (COUNTM) but only if they met certain criteria (COLLECT).
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 405 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!