COUNTIFS/CONTAINS FUNCTION

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.

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

@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 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".

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))

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

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!