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
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 58 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!