COUNTIFS/CONTAINS FUNCTION

Jenn Pitt

They are dropdowns and you can choose multiple selections in the Greenfield one but only one selection in the active In-Progress one.

Jenn Pitt

they are dropdowns with Greenfield it is dropdowns and multi choicks is okay on the Active it is mulit but only one can be chosed.

Jenn Pitt

They are in the same sheet when you say use Column Name: Column Name - where am I using this?

Jenn Pitt

https://community.smartsheet.com/discussion/comment/294818#Comment_294818

What is Column name, column name also the columns are multi dropdowns

Paul Newcome

@Jenn Pitt

[Column Name]:[Column Name]

You would replace "Column Name" with whatever the name of the corresponding column is. The criteria for the dropdowns would be

HAS(@cell, "Active")

and

HAS(@cell, "Greenfield")

=COUNTIFS([Name of Column Housing Active]:[Name of Column Housing Active], HAS(@cell, "Active"), [Name of Column Housing Greenfield]:[Name of Column Housing Greenfield], HAS(@cell, "Greenfield"))

Amy Vanderheyden

@Paul Newcome You are so helpful with these formulas. I have read through several threads and I have tried your different answers to see if any would solve this, and I'm stuck.

Sheet 1 - Sheet with 2021 dates listed used by employees to schedule visits. Each employee gets their own version of Sheet 1.

Sheet 2 - Sheet that lists Events throughout year which would create blackout dates for visits. This sheet has a Start date column, an End date column, and a notes column that will say "No visits allowed". There's also a column that lists applicable locations (US or Canada) as the blackout only applies for those locations.

I need a column that I can use on Sheet 1 to drive conditional formatting so that the entire row changes colors if the date falls within a blackout period. Ideally I'd have one color for US, one for Canada, and one for events that impact both locations. I was thinking a symbol column with the color status balls.

I basically need a formula that says if notes contains "no visits allowed" and the date falls between a start and end date for events in the US the status is Blue, if notes contains "no visits allowed" and the date falls between a start and end date for events in Canada the status is Yellow, and if notes contains "no visits allowed" and the date falls between a start and end date for any location the status is red.

I am able to use the following formula to get the status ball to change if it finds the date, but I am unable to figure out how to add other conditions. I've tried AND, HAS, CONTAINS, etc.

=IF(COUNTIFS({Start}, <=[Visit Date]@row, {End}, >=[Visit Date]@row) > 0, "Blue", "Red")

Paul Newcome

@Amy Vanderheyden You would incorporate the additional range/criteria sets following the same syntax you already used for the Start and End range/criteria sets.

=IF(COUNTIFS({Start}, <=[Visit Date]@row, {End}, >=[Visit Date]@row, {Notes}, "No Visits Allowed") > 0, "Blue", "Red")

Amy Vanderheyden

@Paul Newcome Thank you. I think I still need to use Has or Contains because my Notes column can include other comments beyond just "No Visits Allowed" for example "No Visits Allowed, Annual Recurrence".

Paul Newcome

@Amy Vanderheyden My apologies. I didn't realize your notes column could have additioanl text. Try this...

=IF(COUNTIFS({Start}, <=[Visit Date]@row, {End}, >=[Visit Date]@row, {Notes}, CONTAINS("No Visits Allowed", @cell)) > 0, "Blue", "Red")

Jennifer Melin

okay @Paul Newcome new one for you!

I am trying to count types of products in a column(that is multiselect) based on who sold them. I tried just countifs but it was only counting if the cells that only had 1 selection. So I switched it to FIND but its still not counting what I want.

=COUNTIFS({Onboarding Tracker Range 1}, [Assigned to]@row, {Sold Products}, FIND("Checking account", @cell))

Paul Newcome

@Jennifer Melin There are actually a few ways to do this, but lets just stick with your FIND function approach. What you need to keep in mind for this particular function is that it outputs a NUMBER based on where within a text string the "specific text" is found. If the "specific text" is not found, it will output a zero. If it is found, it will output any number greater than zero that will indicate the position within the string that it starts.

What all of that means is that basically your criteria is if the FIND function outputs a number greater than zero.

=COUNTIFS({Onboarding Tracker Range 1}, [Assigned to]@row, {Sold Products}, FIND("Checking account", @cell) > 0)

Edited to add:

Keep in mind the the FIND function is case sensitive.

Ashlee Chartier

I got this to work for part of my issue however, I have a sheet that has a "Action Required By" multi-select contact column. I just want to count all the times that a name appears in this column. However, no matter what I try I am getting either a 0 or 1 and the name appears more than 5 times..

Here are the formulas I have tried

=COUNT(FIND("Jane Doe", {1 - D1 Builder Pursuits Range 1}, @cell > 0))

=COUNT(CONTAINS("Jane Doe", {1 - D1 Builder Pursuits Range 1}))

=COUNTIFS({1 - D1 Builder Pursuits Range 1}, "Jane Doe", {1 - D1 Builder Pursuits Range 1}, CONTAINS("Jane Doe", @cell))

I have tried every iteration of these formulas and none of them are working. They either return a result of 0 or 1. What am I doing wrong?

Thank you kindly

Paul Newcome

@Ashlee Chartier Try this...

=COUNTIFS({1 - D1 Builder Pursuits Range 1}, FIND("Jane Doe", @cell) > 0)

JamieH

Hi Paul!

I have read through this thread and I am still riding the struggle bus.

I am looking to pull data from one sheet into another. The formula I have below works fine when only searching for one word, "Western" within the specified date frame.

=COUNTIFS({Certification Data Tracker Range 1}, "Western", {Certification Data Tracker Range 2}, (AND(@cell <= DATE(2022, 7, 1))))

However, now I need this same formula to find "Employee Support Broker" and "Vendor Support Broker", total both up, and spit out the number (i.e. if there are 10 Employee Support Brokers and 7 Vendor Support Brokers, the formula would proved "17" as the answer).

Any suggestions on how to achieve this? Thanks!

Genevieve P.

https://community.smartsheet.com/discussion/comment/328022#Comment_328022

Hi @JamieH

You can add two separate COUNTIFS statements together to see a total for 2 Counts:

=COUNTIFS() + COUNTIFS()

For example:

=COUNTIFS({Range 1}, "Employee Support Broker", {Date Range}, @cell <= DATE(2022, 7, 1)) + COUNTIFS({Range 1}, "Vendor Support Broker", {Date Range}, @cell <= DATE(2022, 7, 1))

Note that you don't need to add in the AND function since a COUNTIFS already implies "and'.

Cheers,

Genevieve