Trying to count the ammount of values using Countifs, CONTAINS and "AND" Functions

Struggling with a formulas here and get "Unparesable" error.

Trying to count the ammount of times a value appears in a row when a another cell in the row contains a certain value

Column 1 Name = "Business Line / Function Benefit"

Column 2 Name = "Current Project Phase"

Looking to count where column 1 contains "Asphalt" but only if Column 2 contains "1 - Engage" on the same row... may be going about this all the wrong way of course :)

Formula I have is

Column 1 = "Business Line / Function Benefit"

=COUNTIFS({Business Line / Function Benefit}, CONTAINS("Asphalt", @cell)), AND({Current Project Phase}, ("1 - Engage", @cell))

Any help appreciated

Answers

  • Samuel Mueller
    Samuel Mueller Overachievers

    @Jimthegem Try this

    =COUNTIFS({Business Line / Function Benefit}, CONTAINS("Asphalt", @cell), {Current Project Phase}, contains("1 - Engage", @cell))

    however, the curly brackets indicate a cross sheet reference but that doesn't sound like what you have .if your formula and columns are on the same sheet you need this formula

    =COUNTIFS([Business Line / Function Benefit]:[Business Line / Function Benefit], CONTAINS("Asphalt", @cell), [Current Project Phase]:[Current Project Phase], contains("1 - Engage", @cell))

  • Thanks Samuel

    The values are on a another sheet so yes a cross sheet reference is needed on this one..

    =COUNTIFS({Business Line / Function Benefit}:{Business Line / Function Benefit}, CONTAINS("Asphalt", @cell), {Current Project Phase}:{Current Project Phase}, ("1 - Engage", @cell))

    Does this look correct? I did wonder if there should be another COUNTIFS after the contains...perhaps i am over complicating it...?

  • This formula works

    =COUNTIFS({Business Line / Function Benefit}, CONTAINS("Asphalt", @cell))

    Just needs the extra criteria added..

  • Samuel Mueller
    Samuel Mueller Overachievers

    @Jimthegem My first formula should have worked for you then:

    =COUNTIFS({Business Line / Function Benefit}, CONTAINS("Asphalt", @cell), {Current Project Phase}, contains("1 - Engage", @cell))

    Does this work?

  • Works a treat! Thank you so much that has saved me hours :)

    Thanks Samuel

  • Samuel Mueller
    Samuel Mueller Overachievers

    Happy to help!

  • One last one... thought i had it now trying to do a similar thing but using a checkbox "Checked" to create the count.

    Recieving an Unparsable error...

    =COUNTIFS({Mandatory Delivery}, =1), {Current Project Phase}, CONTAINS("1 - Engage", @cell))

    any ideas?

  • Samuel Mueller
    Samuel Mueller Overachievers

    @Jimthegem looks like you have a parenthesis in the wrong spot is all:

    =COUNTIFS({Mandatory Delivery}, 1, {Current Project Phase}, CONTAINS("1 - Engage", @cell))

    for future, when you are typing out a formula and your references aren't colored, that usually indicates that there is an error in your syntax somewhere, just follow the guide that pops up when you start typing a formula!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!