Use text contained within Cell Reference in COUNTIFS function

timclare

Hi, I'm trying to develop a "Countifs" function that counts the number of times a particular text string is referenced in a separate sheet.

In the separate sheet, the range is a multi-select drop-down box, so the cells that have multiple options selected are omitted because the formula is looking for the full text string.

Is there a way to use "Contains" with a reference to search for part of the cell?

I've tried to show an example - basically Sheet 1 is the record of what each customer bought in each week, and sheet 2 is the summary.

Given Smartsheet separates multiple entries by a space, is there any way to do this?

Andrée Starå

Hi @timclare

I hope you're well and safe!

Yes, that should work.

=COUNTIFS([Week 1]:[Week 1], CONTAINS(Reference@row, @cell))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Sandhiya07

Hi @timclare

Hope You are doing Good and Safe.

Yes we can use "Contains" like the below,

So in your case it will be like,

=COUNTIFS([Week 1]:[Week 1], CONTAINS("Fruit", @cell))

So you can refer other sheet instead of directly specifying column name.

Hope this helps.

Thanks,

Sandhiya P

timclare

Thanks Sandhiya, I am well thanks and hope you are too.

I was wondering if it's possible to use a reference instead of the actual text within the formula. So in your case instead of "Updated" I would like to use a reference to a cell that contains "Updated".

So in my case, instead of CONTAINS("Fruit") I was hoping to use CONTAINS([**REFERENCE**]) but I don't think this works.

Any ideas?

Andrée Starå

Hi @timclare

I hope you're well and safe!

Yes, that should work.

=COUNTIFS([Week 1]:[Week 1], CONTAINS(Reference@row, @cell))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

timclare

Great thanks heaps, that worked. I missed the final , "@cell" component of the CONTAINS() function

Andrée Starå

@timclare

Excellent!

Happy to help!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

JSNelan

I am using a Countifs formula to count how many times workshops are requested by state. I pull the date in from a separate sheet. I am able to drag the formula down for each state. However, I have to manually type in the state abbreviation for each state into each formula “AL”, etc. how do I change the “” to just pull the text from the row I am on?

JSNelan

meant to say: I am using a Countifs formula to count how many times workshops are requested by state. I pull the DATA in from a separate sheet. I am able to drag the formula down for each state. However, I have to manually type in the state abbreviation for each state into each formula “AL”, etc. how do I change the “” to just pull the text from the row I am on?