Formula for countif and contains

Jaz693

I am having trouble creating formula that adds two fields. The first field must contain the word strategy, and the second field can be one of several status. Here is one attempt of the formula:

=COUNTIFS([Project Type]:[Project Type],CONTAINS("Strategy", @cell), AND(COUNTIFS([Project Phase]:[Project Phase], “Execution”, [Project Phase]:[Project Phase], “Planning”, [Project Phase]:[Project Phase], “Initiation”, [Project Phase]:[Project Phase], “Vendor Selection”, [Project Phase]:[Project Phase], “Monitoring/Controlling”, [Project Phase]:[Project Phase], “Combined into another Project”, [Project Phase]:[Project Phase], “On-Hold”))))

Not sure where this failing

Gia Thinh

I found that we got a typing error in the formula as below:

Retry this formula:

=COUNTIFS([Project Type]:[Project Type], CONTAINS("Strategy", @cell)) + COUNTIFS([Project Phase]:[Project Phase], OR(@cell = "Planning", @cell = "Execution", @cell = "Initiation", @cell = "Vendor Selection", @cell = "Monitoring/Controlling", @cell = "Combined into another Project"))

Jaz693

That worked! Thank you so much!!

joelsmit94161

Did you see this page?

https://community.smartsheet.com/discussion/24536/help-on-formula-count-if-with-multiple-criteria

I wonder if it may help?

Jaz693

Unfortunately no> I went to that one and tried several other variations:

=COUNTIF(CONTAINS(([Project Type]:[Project Type]), "Strategy")),COUNTIFS([Project Phase]:[Project Phase], "Execution") + COUNTIFS([Project Phase]:[Project Phase], "Initiation") + COUNTIFS([Project Phase]:[Project Phase], "Vendor Selection") + COUNTIFS([Project Phase]:[Project Phase], "Planning") + COUNTIFS([Project Phase]:[Project Phase], "Monitoring/Controlling") + COUNTIFS([Project Phase]:[Project Phase], "Combined into another Project")

OR

=COUNTIFS((([Project Type]:[Project Type], “RCU Strategy”)+ COUNTIF(([Project Type]:[Project Type], “RCUSG Strategy”))), [Project Phase]:[Project Phase], OR(@cell = "Planning", @cell = "Execution", @cell = "Initiation", @cell = “Vendor Selection”, @cell = “Monitoring/Controlling”, @cell = “Combined into another Project”))

I'm stumped. Do I have too many parenthesis? not enough arguments? The error I receive is Unparseable regardless of the changes I make

Gia Thinh

Hi

In case you want to add 2 fields, I think we may split your original formula as below:

=COUNTIFS([Project Type]:[Project Type],CONTAINS("Strategy", @cell) + COUNTIFS([Project Phase]:[Project Phase], OR(@cell = "Planning", @cell = "Execution", @cell = "Initiation", @cell = “Vendor Selection”, @cell = “Monitoring/Controlling”, @cell = “Combined into another Project”))

Jaz693

Getting closer, Im still getting unparseable with the above. Once I start to enter the Project Phase variables the color coding of the field names go away, almost like they arent recognizable anymore????

Gia Thinh

Can you show some screenshots to demonstrate the situation?

Jaz693

screenshots.docx

I think this should give you an idea

Gia Thinh

I found that we got a typing error in the formula as below:

Retry this formula:

=COUNTIFS([Project Type]:[Project Type], CONTAINS("Strategy", @cell)) + COUNTIFS([Project Phase]:[Project Phase], OR(@cell = "Planning", @cell = "Execution", @cell = "Initiation", @cell = "Vendor Selection", @cell = "Monitoring/Controlling", @cell = "Combined into another Project"))

Jaz693

That worked! Thank you so much!!