Count Multiple Group Membership

Monica Gallegos

I want to answer the question: How many members belong to more than one group?

My sheet contains the names and emails of individuals and columns with check boxes for each group.

I have a metric sheet that I anticipate putting the COUNTIFS formula. But I'm not sure how to bring it together so it counts if more than one check box is a row is selected.

Any guidance would be appreciated.

Genevieve P.

Hi @Monica Gallegos,

Since you could have any type of combination of groups, the easiest thing to do would actually be to set up a "helper" column in this current source sheet that does a simple COUNT of all the checkboxes in its row. You can set this as a Column Formula and then even hide it on the sheet, if you don't want to clutter up the view.

Then in your metrics sheet you can use your COUNTIFS to reference the name of the user and if this helper column is greater than 1. Does that make sense?

Here's an example of the helper COUNT formula in the source sheet:

=COUNTIF([First Group]@row:[Last Group]@row, 1)

Note: you'll need to swap out the column names for the ones in your sheet.

Cheers!

Genevieve

Monica Gallegos

Thanks Genevieve for this guidance! The Helper Column wasn't something that even occurred to me but makes complete sense for this exercise.

Along with your =COUNTIF([First Group]@row:[Last Group]@row, 1) - updated with my column names...

I then used multiple COUNTIFS formula =COUNTIFS({ERG MEMBERSHIP - Helper Column}, 2), to indicate if a member belonged to 1 group, 2 groups, 3 groups, etc. I figured the team would eventually want to know this info at a more granular level.

=COUNTIFS({ERG MEMBERSHIP - Helper Column}, 2)

Hope others find this useful! Thanks again for the great advice!⭐️

Genevieve P.

No problem at all! I'm glad that this works for you.

Thank you for posting your final solution, looks great!! 🙂