Summary Report - COUNTIF of multi-selection columns

Dennis Wierzbicki

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

Paul Newcome

You need to work in a CONTAINS or FIND function. CONTAINS is a little bit easier to use, but does not work on contact type columns.

=COUNTIFS(Priority:Priority, "High", [Deal Lead]:[Deal Lead], CONTAINS([Deal Lead]#, @cell))

=COUNTIFS(Priority:Priority, "High", [Deal Lead]:[Deal Lead], FIND([Deal Lead]#, @cell) > 0))

Paul Newcome

You need to work in a CONTAINS or FIND function. CONTAINS is a little bit easier to use, but does not work on contact type columns.

=COUNTIFS(Priority:Priority, "High", [Deal Lead]:[Deal Lead], CONTAINS([Deal Lead]#, @cell))

=COUNTIFS(Priority:Priority, "High", [Deal Lead]:[Deal Lead], FIND([Deal Lead]#, @cell) > 0))

Dennis Wierzbicki

Paul, I stumbled on the HAS function before reading your suggestion of using CONTAINS, and it did the trick, too.

THANKS!

Dennis Wierzbicki

Here's something odd, though, that I've never seen: when I pull out the Sheet Summary, and use it, then put it away, Smartsheet thinks a change has been made to the sheet itself, even when I've not made any changes.

Thoughts?

Paul Newcome

What do you mean by "Smartsheet thinks a change has been made to the sheet itself"?

Dennis Wierzbicki

The floppy disc "ungreys" itself, and if I try to leave the sheet, I'm asked if I want to save the changes, even though all I've done is pull out the Sheet Summary, select a couple drop down choices, then put it away.

Andrée Starå

Hi @Dennis Wierzbicki

It’s probably because you have a formula with the TODAY function or similar in the sheet.

Do you have something like that?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Dennis Wierzbicki

Andree, no, I thought of that. If I just open the sheet, and close it, I don't get the same message. Something is happening inside the sheet to cause it to act as though a change has been made, when in fact, nothing has been changed; all I've done is opened, used, and closed the Sheet Summary.

Paul Newcome

The sheet summary is part of the sheet. The changes you are making in the summary are what's triggering it.

Andrée Starå

@Dennis Wierzbicki

Paul is correct. The Sheet Summary is part of the Sheet so that is triggering the change.