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

Options

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

• Overachievers
Options

@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))

• Options

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...?

• Options

This formula works

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

Just needs the extra criteria added..

• Overachievers
Options

@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?

• Options

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

Thanks Samuel

• Overachievers
Options

Happy to help!

• Options

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?

• Overachievers
Options

@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!