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

Jimthegem

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

Samuel Mueller

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

Jimthegem

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

Jimthegem

This formula works

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

Just needs the extra criteria added..

Samuel Mueller

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

Jimthegem

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

Thanks Samuel

Samuel Mueller

Happy to help!

Jimthegem

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

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