How to Count Multiple Drop Down Values

Miles Allison

I have formula to count initials in a particular column.

Column:

RB

CB

EB

My current COUNTIF formula will count 1 for RB 1 for CB and 1 for EB. However, I cant seem to get the COUNTIF formula to recognize if multiple selections have been made for one row. If a row says RB CB it will show as O.

Thanks,

Miles

Mark Cronk

Hi @Miles Allison ,

There are 2 unique functions in Smartsheet to deal with multiple select fields; HAS and COUNTM.

COUNTM: Counts the number of elements in a multi-contact or multi-select dropdown column cell or cell range. Returns the total number of elements found. https://help.smartsheet.com/function/countm

HAS: Search for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found.https://help.smartsheet.com/function/has

You'll want to use COUNTM here. The syntax is: =COUNTM( search_range1, [ search_range2​... ]) Just replacing your COUNTIF with COUNTM may be enough.

Help?

Mark

Nic Larsen

Try this: =COUNTIFS([Column Name]:[Column Name], CONTAINS("RB", @cell))

If you are just trying to count each cell individually and see how many selections were made you can use the COUNTM function. I wasn't totally sure which direction you were going.

Mark Cronk

Hi @Miles Allison ,

There are 2 unique functions in Smartsheet to deal with multiple select fields; HAS and COUNTM.

COUNTM: Counts the number of elements in a multi-contact or multi-select dropdown column cell or cell range. Returns the total number of elements found. https://help.smartsheet.com/function/countm

HAS: Search for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found.https://help.smartsheet.com/function/has

You'll want to use COUNTM here. The syntax is: =COUNTM( search_range1, [ search_range2​... ]) Just replacing your COUNTIF with COUNTM may be enough.

Help?

Mark

Miles Allison

I ended up making a reference column using IF(HAS). Thanks for the help everyone!

Mark Cronk

Happy to help. Thanks for using the Community.

Mark

DeMetria Hines

I have a column that has this data:

Draft

Submitted

Screening

Completed

I need count how many times Draft, Submitted, and Screening are selected as a total, not individually.

Help!

Genevieve P.

Hi @DeMetria Hines

Is this column a multi-select column or single-select?

You can add multiple COUNTIF statements together, like so:

=COUNTIF([Column Name]:[Column Name], "Draft") + COUNTIF([Column Name]:[Column Name], "Submitted") + COUNTIF([Column Name]:[Column Name], "Screening")

However if it's a multi-select column, you'll need to use the function HAS as well, to see if the cell has that specific selection along with others:

=COUNTIF([Column Name]:[Column Name], HAS(@cell, "Draft")) + COUNTIF([Column Name]:[Column Name], HAS(@cell, "Submitted")) + COUNTIF([Column Name]:[Column Name], HAS(@cell, "Screening"))

Cheers,

Genevieve

DeMetria Hines

I will try that, thank you!

DeMetria Hines

That worked! But now I am asked to break it down by qtr, and I am not sure how to make the date function work, I have tried it before the plus sign, after, and its not working.

So same scenario but adding per qtr to it. @Genevieve P.

Genevieve P.

Hi @DeMetria Hines

If you're looking into one Date column for your Quarter Dates, then yes! I would personally use the DATE function to do this.

Here's how you would list the column and criteria:

[Date Column]:[Date Column], >=DATE(yyyy, mm, dd), [Date Column]:[Date Column], <=DATE(yyyy, mm, dd)

So in a COUNTIF, looking from Jan - Mar 2023, it would look like this:

=COUNTIFS([Column Name]:[Column Name], HAS(@cell, "Draft"), [Date Column]:[Date Column], >=DATE(2023, 01, 01), [Date Column]:[Date Column], <=DATE(2023, 03, 31))

You'll want to add that into each of your separate COUNTIF statements. 🙂

Cheers,

Genevieve

DeMetria Hines

=COUNTIF({Demand State}, HAS({Demand State}, "Draft"), DATE({Demand Opened Date}, >=DATE(2023, 1, 1), {Demand Opened Date}, <=DATE(2023, 3, 31)))

This is the formula that I am putting in and I am still getting Incorrect Argument as my result. Help!

Genevieve P.

Hi @DeMetria Hines

In the HAS function you'll need to use @cell to tell the function to search each individual cell within the previous range, like so:

HAS(@cell, "Draft")

I also see that you've added an extra DATE function in there. The DATE function enables you to write out a date (such as January 1st, 2023) similar to using quotes to spell text. Does that make sense?

This means you'll only need it as a criteria after a {range}, like so:

=COUNTIF({Demand State}, HAS(@cell, "Draft"), {Demand Opened Date}, >=DATE(2023, 1, 1), {Demand Opened Date}, <=DATE(2023, 3, 31))

Try that instead 🙂

DeMetria Hines

=COUNTIF({Demand State}, HAS(@cell, "Draft"), {Demand Opened Date}, >=DATE(2023, 1, 1), {Demand Opened Date}, <=DATE(2023, 3, 31))

I am still getting an error. Not sure what I am doing wrong here.

Genevieve P.

My apologies! It needs to be COUNTIFS plural, with an S:

=COUNTIFS({Demand State}, HAS(@cell, "Draft"), {Demand Opened Date}, >=DATE(2023, 1, 1), {Demand Opened Date}, <=DATE(2023, 3, 31))