Combining IF and COUNTM?

Amber Eakin

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!

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

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!

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