Summarizing multiple multiselect columns
Good morning,
I have 5 project managers using the same template of a sheet to track their current ongoing projects in one spot. One of the columns is a multiselect where they must choose all the suitable categories that the project are covering.
Is there any way in my report to summarize how many of each category option have been selected across all of the project manager's selections? For instance, Option 1 has been selected 26 times across the 5 Project manager sheets.
Anybody have any ideas? The closest thing I've managed to find is using a COUNTIF equation and trying to use "find" for the wording, but even that has not brought back accurate results.
=COUNTIFS({Category Column of PM}, FIND("Option 1") > 0)
Answers

@AMCP For multiselect columns, the HAS function is your best bet. HAS searches for the text as a distinct value within a multiselect cell.
=COUNTIFS({Category Column of PM}, HAS(@cell, "Option 1"))
Then to include the remaining 4 sheets, just add additional iterations of the same formula searching the other sheets' category columns:
=COUNTIFS({Category Column of PM}, HAS(@cell, "Option 1")) + COUNTIFS({Category Column of PM Sheet2}, HAS(@cell, "Option 1")) + COUNTIFS({Category Column of PM Sheet3}, HAS(@cell, "Option 1")) ... and so on.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Hi Jeff,
Thanks for the reply, however in your equation provided "=COUNTIFS({Category Column of PM}, HAS(@cell, "Option 1"))" I'm not sure what the @cell I have bolded makes reference to in the equation, leaving me with errors when I try to use it. Do I need to plug a different value in there?
Thanks,

@AMCP What error are you getting?
The way "@cell" works is to tell the function to check each cell in the range individually for the criteria. So for the {Category Column of PM}, check each cell to see if it HAS a value of "Option 1" in it.
Here it is working in my test sheet:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!