Options
Jenna Corso ✭
01/13/20 in Formulas and Functions
Hello,
I have a contact column where some rows have more than one person assigned to a task. I want to count the number of tasks assigned to any given individual. This is the formula that I have created to do this:
=COUNTIF(CONTAINS("Bernie Sanders", [Assigned]:[Assigned]), "True")
The return value that I get is "0," which is not correct.
Is there another formula that I can try?
Tags:
- Formulas
- functionality
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
01/13/20 Answer ✓
Options
My apologies. I keep forgetting the the CONTAINS function doesn't like contact type columns (which I assume your Assigned column is). Let's try the FIND function instead. Just keep in mind the FIND is case sensitive.
=COUNTIFS(Assigned:Assigned, FIND("Colonel Sanders", @cell) > 0)
8 · Share on FacebookShare on Twitter
Answers
Paul Newcome ✭✭✭✭✭✭
01/13/20
Options
It's a syntax issue. Try this...
=COUNTIFS(Assigned:Assigned, CONTAINS("Colonel Sanders", @cell))
(changed the name because I personally like fried chicken better than politics 😂)
2 · Share on FacebookShare on Twitter
Jenna Corso ✭
01/13/20
Options
This is also returning a "0" :(
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
01/13/20 Answer ✓
Options
My apologies. I keep forgetting the the CONTAINS function doesn't like contact type columns (which I assume your Assigned column is). Let's try the FIND function instead. Just keep in mind the FIND is case sensitive.
=COUNTIFS(Assigned:Assigned, FIND("Colonel Sanders", @cell) > 0)
8 · Share on FacebookShare on Twitter
Jenna Corso ✭
01/13/20
Options
This formula is returning unparseable.
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
01/13/20
Options
@Jenna Corso Hmm... That's odd... Can you copy/paste the exact formula from your Smartsheet to here along with a quick screenshot of your column names?
0 · Share on FacebookShare on Twitter
Jenna Corso ✭
01/13/20
Options
@Paul Newcome I forgot the comma before FIND--it works! Thank you so much for your help!
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
01/13/20
Options
Good catch. Happy to help! 👍️
0 · Share on FacebookShare on Twitter
Brii ✭✭
11/04/21
Options
intente hacer lo que dice @Paul Newcome pero no me funciona, como es que lo escribisste @Jennacorso?? :(
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
11/04/21
Options
@Brii I believe that @Jenna Corso ended up with the same formula I had provided.
=COUNTIFS(Assigned:Assigned, FIND("Colonel Sanders", @cell) > 0)
0 · Share on FacebookShare on Twitter
Brii ✭✭
11/04/21
Options
@Paul Newcome lo escribí tal cual, pero no me arroja nada :(
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
11/04/21
Options
@Brii You need to put [square brackets] around each of the column names. When your column name has a space, number, and/or special character, it needs square brackets around it to be recognized in a formula.
[Column Name]:[Column Name]
0 · Share on FacebookShare on Twitter
Brii ✭✭
11/04/21
Options
muchas gracias @Paul Newcome si funciono, me faltaban los corchetes :P jeje lo siento soy novata en eso
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
11/05/21
Options
@Brii No worries and happy to help. 👍️
0 · Share on FacebookShare on Twitter
Chris Grinstead ✭✭✭
11/29/21
Options
Hey there,
I'm having some issues with the formula being #No Match. I have the referenced column being matched from a Vlookup from another table. Would that cause an issue?
Thanks!
0 · Share on FacebookShare on Twitter
Genevieve P. Employee Admin
11/30/21
Options
Hi @Chris Grinstead
Formula errors can be like dominoes... if there is even one cell in the column "Operating System" that says "NO MATCH", this will then appear in any formula referencing the entirety of that column.
Try adding an IFERROR statement around the VLOOKUP so that any errors appear as blank cells instead, like so:
=IFERROR(VLOOKUP([Asset Name]@row, {SMH_Computers_11182021 Range 1}, 5, false), "")
Then once your Operating System column is fixed, any formula referencing this column should also be fixed.
Cheers,
Genevieve
0 · Share on FacebookShare on Twitter
Help Article Resources
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- 59.3K Get Help
- 88 Global Discussions
- 92 Industry Talk
- 398 Announcements
- 12 Community Corner Newsletter
- 71 Brandfolder
- 121 Just for fun
- 42 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 250 Events
- 13 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
