Use text contained within Cell Reference in COUNTIFS function
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?
Best Answer
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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
-
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?
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Great thanks heaps, that worked. I missed the final , "@cell" component of the CONTAINS() function
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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?
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!