Summary Report - COUNTIF of multi-selection columns

Summary Report - COUNTIF of multi-selection columns

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

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

    THANKS!

  • edited 05/21/20

    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 NewcomePaul Newcome ✭✭✭✭✭

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

  • 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å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!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • edited 05/21/20

    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 NewcomePaul 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åAndrée Starå ✭✭✭✭✭
    edited 05/22/20

    @Dennis Wierzbicki

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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.