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

Tags:

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!