Summarizing multiple multi-select columns

AMCP

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 multi-select 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)

Jeff Reisman

@AMCP For multi-select columns, the HAS function is your best bet. HAS searches for the text as a distinct value within a multi-select 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.

AMCP

Jeff Reisman

Feb 15, 2023

@AMCP For multi-select columns, the HAS function is your best bet. HAS searches for the text as a distinct value within a multi-select 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.

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,

Jeff Reisman

@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: