How to use COUNTM Formula

Lucy Gu

Hi team,

I currently have such a need, take the following situation as an example.

I want to count the quantity of each sector. Multiple choices also need to be counted. For example, one sector chose both industrial and others. So when we calculate the sector, industrial has two and others have one.

How can such a requirement be represented by the COUNTM formula? Thank you!

Protonsponge

Hello @Lucy Gu

CountM is useful for counting the number of elements in a multicontact or multiselect drop down column.

As an example, the formula returns the number of selections in [Multi Select Drop Down]

=COUNTM([Multi Select Drop Down]@row)

If you are looking to count the number of number of times a sector is selected in a column, a COUNTIF function as per below my be helpful to you.

=COUNTIF([Experienced sector (s) *for AD roles]:[Experienced sector (s) *for AD roles], HAS(@cell, "Industrial"))

You could make use of the sheet summary and have a field for each sector and adjusting the above formula, changing "Industrial" for each sector:

I hope that is helpful to you in some way,

Protonsponge

Protonsponge

Hello @Lucy Gu,

Great, pleased you got something that works for you.

The HAS function "searches for an exact match of a single value, including if this value appears in a multicontact or multiselect dropdown column cells or ranges with other values. If HAS finds the exact match, it returns "True." Otherwise, it returns "False."

The @cell "argument performs a calculation on each row at the same time that the primary function is evaluating the criteria in the range."

If we take the formula below - In the first part, we apply the COUNTIF function and specify the range of your [Experienced sector(s) *for AD roles] column. The IF in our COUNTIF checks to determine if a cell HAS "Industrial" in it for each cell (@cell) in the column.

=COUNTIF([Experienced sector (s) *for AD roles]:[Experienced sector (s) *for AD roles], HAS(@cell, "Industrial"))

There is a fabulous resource that could be of interest to you. Its the Smartsheet formula hand book and it details all of the functions in Smartsheet. it can be found here - LINK

Protonsponge

Protonsponge

Hello @Lucy Gu

CountM is useful for counting the number of elements in a multicontact or multiselect drop down column.

As an example, the formula returns the number of selections in [Multi Select Drop Down]

=COUNTM([Multi Select Drop Down]@row)

If you are looking to count the number of number of times a sector is selected in a column, a COUNTIF function as per below my be helpful to you.

=COUNTIF([Experienced sector (s) *for AD roles]:[Experienced sector (s) *for AD roles], HAS(@cell, "Industrial"))

You could make use of the sheet summary and have a field for each sector and adjusting the above formula, changing "Industrial" for each sector:

I hope that is helpful to you in some way,

Protonsponge

Lucy Gu

Hi @Protonsponge ,much thanks for your reply. I have successfully used this formula for statistics. But I want to know what "HAS" means and how do I use it? Why need to use "@cell"? Thank you!

Protonsponge

Hello @Lucy Gu,

Great, pleased you got something that works for you.

The HAS function "searches for an exact match of a single value, including if this value appears in a multicontact or multiselect dropdown column cells or ranges with other values. If HAS finds the exact match, it returns "True." Otherwise, it returns "False."

The @cell "argument performs a calculation on each row at the same time that the primary function is evaluating the criteria in the range."

If we take the formula below - In the first part, we apply the COUNTIF function and specify the range of your [Experienced sector(s) *for AD roles] column. The IF in our COUNTIF checks to determine if a cell HAS "Industrial" in it for each cell (@cell) in the column.

=COUNTIF([Experienced sector (s) *for AD roles]:[Experienced sector (s) *for AD roles], HAS(@cell, "Industrial"))

There is a fabulous resource that could be of interest to you. Its the Smartsheet formula hand book and it details all of the functions in Smartsheet. it can be found here - LINK

Protonsponge

Lucy Gu

Hi @Protonsponge . I got it. Very appreciate for your help. This link is so useful! Thank you very much！！😊