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
-
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))
Answers
-
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, I stumbled on the HAS function before reading your suggestion of using CONTAINS, and it did the trick, too.
THANKS!
-
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?
-
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.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
The sheet summary is part of the sheet. The changes you are making in the summary are what's triggering it.
-
Paul is correct. The Sheet Summary is part of the Sheet so that is triggering the change.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!