Multi select checkbox count checked items

ahall_8016

I have a multi-select checkbox that I am trying to count checked items in the cell and total in another cell

 

In the column invest strategy capability I want to count items checked in the cell with the first arrow and put the total in the check mark under invest total.  Same for the second arrow.

I have seen the Countif and Contains function but im unsure how that would work.  Any help would be appreciated

Paul Newcome

So to be clear... You are wanting to look in a single cell and count how many different selections were made?

ahall_8016

Hi Paul,

Yes that is correct. 

Thank you

Paul Newcome

At this time, there isn't a way to directly count how many different entries were selected within the single cell. From what I understand though, there is something on its way.

 

In the mean time, I have a few ideas for some different solutions, but the best fit is going to depend on some things. Most importantly... What is the maximum amount of options that could be selected? Basically... What would be the count if every option was selected within the cell?

Paul Newcome

Ignore my above post... I was making things harder than they had to be. Here's the idea...

 

The number of characters (including the delimiter of CHAR(10)) minus the number of characters after removing the delimiter. Add 1 to account for that final (or only) entry that does not have a delimiter after it, and that will tell you how many delimiters (+1) were used in the cell. 1 delimiter per selection +1 for the final selection, and that will tell you how many selections were made.

 

To find the total number of characters including the delimiter...

 

=LEN([Invest Strategy Capability]@row)

.

To remove the delimiters

=SUBSTITUTE([Invest Strategy Capability]@row, CHAR(10), "")

.

Now we find how many characters are left after removing the delimiters

=LEN(SUBSTITUTE([Invest Strategy Capability]@row, CHAR(10), ""))

.

Now subtract the second from the first and add 1 and that will tell you how many selections were made....

=LEN([Invest Strategy Capability]@row) - LEN(SUBSTITUTE([Invest Strategy Capability]@row, CHAR(10), "")) + 1

ahall_8016

Smooth Paul, simply smooth.  Much appreciated. You have saved my bacon today!

Paul Newcome

Happy to help! 

ahall_8016

Although, if nothing is selected it still shows 1.  How would I put this into a workable format?

 IF(ISBLANK([Invest Strategy Capability]@row, 0, =LEN([Invest Strategy Capability]@row) - LEN(SUBSTITUTE([Invest Strategy Capability]@row, CHAR(10), "")) + 1

Paul Newcome

This will work if you move the = to the beginning and close the ISBLANK function.

 

=IF(ISBLANK([Invest Strategy Capability]@row), 0, LEN([Invest Strategy Capability]@row) - LEN(SUBSTITUTE([Invest Strategy Capability]@row, CHAR(10), "")) + 1)

.

Basically... The = in the beginning tells Smartsheet that you are starting a formula or some sort of command.

.

Your formula is saying "If the cell is blank, output a zero, otherwise run this other formula".

 

My PERSONAL preference is to say "If the cell is text, run this formula, otherwise output a zero".

Not to say that your solution is incorrect. It absolutely will work with those two tweaks of moving the = and closing the ISBLANK. I simply bring this up to show that there can be multiple solutions that all produce the same result.

 

=IF(ISTEXT([Invest Strategy Capability]@row), LEN([Invest Strategy Capability]@row) - LEN(SUBSTITUTE([Invest Strategy Capability]@row, CHAR(10), "")) + 1, 0)

.

Its just how my personal thought process flows. I look at my end goal and think to myself

Step 1: I want to do this.

Step 2: This will work if that is text.

Step 3: What if it isn't text?

.

Again... Not to say you are wrong. Just a different perspective.

ahall_8016

Paul I definitely appreciate the help and perspective.  Relatively new to the equation game so the teaching experience is very valuable!  Have a good one

 

Thanks

James

Paul Newcome

Happy to help! 

 

There are a lot of different tips and tricks, and each one of us does certain things just a little bit differently. It all depends on how you learned, what you are comfortable with, and most importantly what works for you.

 

Don't hesitate to ask away here in the Community. Even if it seems like a "simple" question... Ask it. I certainly don't have all of the answers, but with the collective knowledge here there isn't much you can't find or get an answer to.

Shawn Borror

Has this been resolved or additional functionality added? I am trying to count distinct contract types but my column for contract types allows for multi-select. I've tried this

COUNT({Legal Work Queue Range 6}, CONTAINS("MDA"))

But this doesn't count properly.

Ezra

I thought that the most clever method was counting the commas in a multiple contact column and adding 1.

=IF(Users@row <> "", LEN(Users@row) - LEN(SUBSTITUTE(Users@row, ",", "")) + 1, 0)