Formula Issue of counting texts that contains more than one in one column

Shirley Vue

I'm having issues with this formula, "=COUNTIF(CONTAINS({Tracking Sheet Range 1}, "Motorola", ({Tracking Sheet Range 3}, "In Progress")))" The term, 'Motorola' is in multiple cells in the same column and is used as a multiple dropdown menu option. Can someone please help?

Paul Newcome

So in the formula...

=COUNTIFS({Tracking Sheet Range 1}, CONTAINS("Motorola", @cell), {Tracking Sheet Range 3}, "In Progress")

{Tracking Sheet Range 1} should be the entire column that "Motorola" would be listed in.

and

{Tracking Sheet Range 3} should be the entire column that "In Progress" would be listed in.

Shirley Vue

Trying to play around with this formula as well, "=COUNTM(CONTAINS({Tracking Sheet Range 1}, "Motorola", IF(AND({Tracking Sheet Range 3}, "In Progress", IF(AND({Tracking Sheet Range 4}, "Q1_2020", IF(OR({Tracking Sheet Range 4}, "Q2_2020", IF(OR({Tracking Sheet Range 4}, "Q3_2020", IF(OR({Tracking Sheet Range 4}, "Q4_2020"))))))))))))"...only gives me 1 as a default answer when there are multiple entries.

Paul Newcome

Try something like this...

=COUNTIFS({Tracking Sheet Range 1}, CONTAINS("Motorola", @cell), {Tracking Sheet Range 3}, "In Progress")

Shirley Vue

Yes, it works for one cell but I have multiple products that have multiple entries I want to capture.

Paul Newcome

Are you able to provide a screenshot of the source data and the target sheet with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

Being able to see exactly what you are working with and what you are trying to accomplish would be very helpful.

EDIT:

What exactly are your different ranges covering? Are you selecting a single cell or the entire column you are trying to evaluate?

Shirley Vue

I'm creating a metrics widget and have it graphed on dashboards.

So I need to find a formula that will use a different sheet for reference to calculate the numbers and have it graphed on Dashboards.

Paul Newcome

Yes. So if the ranges are set to look at the entire column (each appropriate column), then the formula I provided should work.

Paul Newcome

So in the formula...

=COUNTIFS({Tracking Sheet Range 1}, CONTAINS("Motorola", @cell), {Tracking Sheet Range 3}, "In Progress")

{Tracking Sheet Range 1} should be the entire column that "Motorola" would be listed in.

and

{Tracking Sheet Range 3} should be the entire column that "In Progress" would be listed in.

Shirley Vue

It works! Thank you!

Paul Newcome

Happy to help! 👍️