Summary Report - COUNTIF of multi-selection columns

Dennis Wierzbicki
Dennis Wierzbicki ✭✭✭✭
edited 05/21/20 in Formulas and Functions

I love the way a Summary Report can kinda be a poor man's pivot table within Smartsheet, and it works really well, up until you try to do conditional counts of columns with multiple selections. See below.

What's happening here is I want to allow a COUNTIFS when a person is chosen, and count the number of instances data meeting another criteria in other columns matches. The formula above works fine with the "Deal Lead" column is a single selection drop down (or, in this case, a single selection contact column), but when I allow multiple selections in the Deal Lead column, and/or when I allow multiple selections in other columns, things don't work.

What the above formula does is return the number of instances when the Deal Lead is the ONLY person selected as Deal Lead. BTW, [Deal Lead]# finds the Deal Lead that is selected in the Summary Report.

I understand there is a COUNTM function that counts the number of entries in a multiple selection column, but apparently no equivalent COUNTIFM or COUNTIFSM.

Also, I know I could do this with an off-sheet metrics sheet and cross sheet references, but that sort of defeats the purpose and value of the Summary Report function, no?

Any ideas?

Thanks, Dennis

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!