Combining IF and COUNTM?
Hello, all! I need to count items in a multiple option column, but only within a certain date range. If this were a single selection, I would use COUNTIFS, but this is different. Do you have advice on how to do this?
I appreciate any guidance you can offer!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
Answers
-
Hey @Amber Eakin
Depending on exactly what you're looking for, you may be able to use COUNTIFS with either HAS or CONTAINS within the COUNTIFS to gather the dropdown values. The HAS function will yield an exact match, Contains will gather data that contains the string. Note the syntax between the two are different.
If the above answer doesn't work for you, can you give us a screenshot and a bit more detail what you're looking for so we can provide the best answer for your situation?
Kelly
-
Thanks, @Kelly Moore ! That's not quite what I'm looking for. I have a form with three categories people can select; for each category, they can select multiple items. I need to count how many research, writing, or non-library (the three categories) topics are addressed within a time frame. COUNTM would give me the topics, but that doesn't allow me to create a timebound feature like COUNTIF would. If only we had a COUNTMIF!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
-
Hey
I was able to get it to work with a COUNTM/COLLECT
I almost always add an ISDATE in my collects (or countifs) as the first date criteria to try to skip any non date fields. Just as a test, I used Month criteria in my sheet to simulate whatever date criteria you might have. My syntax looked like this
=COUNTM(COLLECT([mult-select]:[mult-select], Date:Date, ISDATE(@cell), Date:Date, MONTH(@cell) = 4))
Will something like this work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!