Countif and formula

Katie Aldrich

Hi I am trying to create a formula that counts how many times "Engineering" appears in a column when the column next to it that is either "Delayed, Set Back, At Risk for Delay"

Here is what I tried:

=COUNTIF([Critical Action Department]2:[Critical Action Department]51, CONTAINS("Engineering", @cell)AND([Task Status Based on Due Date]2:[Task Status Based on Due Date]51, CONTAINS("Delayed, At Risk for Delay, Set Back", @cell))

Thank you!

Katie Aldrich

Hi @Bassam Khalil

I need the formula across the top not in a column, please. Thank you!

This is the correct formula from Bassam:

=IFERROR(COUNT(COLLECT([Critical Action Department]:[Critical Action Department],
[Critical Action Department]:[Critical Action Department], CONTAINS(Criteria@row, @cell),
[Task Status Based on Due Date]:[Task Status Based on Due Date],
OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell),
CONTAINS("At Risk for Delay", @cell)))), "")

Bassam Khalil

@Katie Aldrich 

Done as per your request.

=IFERROR(COUNT(COLLECT([Critical Action Department]:[Critical Action Department],
[Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell),
[Task Status Based on Due Date *]:[Task Status Based on Due Date *],
OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))), "")

Tim Shaded

Hello Katie,

I was not quite clear on your needed setup, so below are two formulas that should work, depending on which specific setup is needed:

Option 1. If the second column contains the entire text string for "Delayed, At Risk for Delay, Set Back".

=COUNTIFS([Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], CONTAINS("Delayed, At Risk for Delay, Set Back", @cell))

Option 2. If the second column contains any of the "Delayed", "At Risk for Delay", "Set Back".

=COUNTIFS([Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], CONTAINS("Delayed", @cell))+COUNTIFS([Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], CONTAINS("At Risk for Delay", @cell))+COUNTIFS([Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], CONTAINS("Set Back", @cell))

*Please not that the row numbers are removed, which will review the entire columns, rather than only rows 2 through 51. Hope that won't be an issue. Limiting the rows could pose additional challenge in making the formulas work. Hope this helps.

Gabby Nepomuceno

If "Delayed, At Risk for Delay, Set Back" is one criteria, try moving both criteria inside the "And" formula.

Try:

=COUNTIF(AND([Critical Action Department]2:[Critical Action Department]51, CONTAINS("Engineering", @cell), [Task Status Based on Due Date]2:[Task Status Based on Due Date]51, CONTAINS("Delayed, At Risk for Delay, Set Back", @cell))

If those are three different criteria, you might need both "And" and "Or" arguments.

Try:

=COUNTIF(AND([Critical Action Department]2:[Critical Action Department]51, CONTAINS("Engineering", @cell), (OR([Task Status Based on Due Date]2:[Task Status Based on Due Date]51, CONTAINS("Delayed", @cell),[Task Status Based on Due Date]2:[Task Status Based on Due Date]51, CONTAINS("At Risk for Delay", @cell), [Task Status Based on Due Date]2:[Task Status Based on Due Date]51, CONTAINS("Set Back", @cell))

Katie Aldrich

Hi Tim and Gabby, neither option worked.

Basically I want a countif formula when Engineering appears count how many times only if the task status based on due date column is not on track (delayed, at risk for delay or set back).

It should return the value of 1.

For Marketing, it should return the value of 5.

Bassam Khalil

Hi @Katie Aldrich 

Hope you are fine, please try the following formula:

=IFERROR(COUNT(COLLECT([Critical Action Department]:[Critical Action Department],
[Critical Action Department]:[Critical Action Department], CONTAINS(Criteria@row, @cell),
[Task Status Based on Due Date]:[Task Status Based on Due Date],
OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell),
CONTAINS("At Risk for Delay", @cell)))), "")

the following screenshot shows the result ( i create the formula as per criteria )

Katie Aldrich

Hi @Bassam Khalil

I tried to decipher the formula nd add in the criteria but it still is not working, it still says unparseable.

=IFERROR(COUNT(COLLECT([Critical Action Department]1:[Critical Action Department]60,

[Critical Action Department]1:[Critical Action Department]60, CONTAINS("Engineering" @row, @cell),

[Task Status Based on Due Date]1:[Task Status Based on Due Date]60,

OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell),

CONTAINS("At Risk for Delay", @cell)))))

Katie Aldrich

HI @Bassam Khalil @Tim Shaded @Gabby Nepomuceno

I have also tried this formula seems the closest when I type it in Smartsheet:

OPTION 1:

=COUNTIF([Critical Action Department]1:[Critical Action Department]50, CONTAINS("Engineering", @cell)), AND(COUNTIF([Task Status Based on Due Date *]1:[Task Status Based on Due Date *]50, OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell))))

OPTION 2:

=COUNTIF([Critical Action Department]1:[Critical Action Department]50, CONTAINS("Engineering", @cell)), AND(COUNTIF([Task Status Based on Due Date *]1:[Task Status Based on Due Date *]50, CONTAINS("Delayed", @cell), OR(CONTAINS("Set Back", @cell)), OR(CONTAINS("At Risk for Delay", @cell))))

These formulas work separately but not together.

=COUNTIF([Critical Action Department]1:[Critical Action Department]50, CONTAINS("Engineering", @cell)),

=COUNTIF([Task Status Based on Due Date *]1:[Task Status Based on Due Date *]50, OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))

It should return the value of 1.

Bassam Khalil

@Katie Aldrich

It's working in my sample sheet, please share me as an admin on a copy of your sheet after removing any sensitive data and I will try to fix it for you.

Katie Aldrich

Hi @Bassam Khalil

There is too much information for me to share this sheet with you. The formula you provided doesn't have any row ranges, is this why it isn't working?

The formulas I have work separately but they don't work combined. How do I combine them to return the value of 1 for Engineering like my example?

=COUNTIF([Critical Action Department]1:[Critical Action Department]50, CONTAINS("Engineering", @cell)),

=COUNTIF([Task Status Based on Due Date *]1:[Task Status Based on Due Date *]50, OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))

Bassam Khalil

@Katie Aldrich

The range in My formula contain the whole column, I don't want you to share the original sheet.

Save a copy of your sheet and delete all sensitive data in this copy and keep a sample data only to test the formula then share me as an admin on this copy to check what is the problem.

Katie Aldrich

@Bassam Khalil I have shared a sample sheet.

Bassam Khalil

Hi @Katie Aldrich 

I fix it please check it.

Katie Aldrich

Hi @Bassam Khalil

I need the formula across the top not in a column, please. Thank you!

This is the correct formula from Bassam:

=IFERROR(COUNT(COLLECT([Critical Action Department]:[Critical Action Department],
[Critical Action Department]:[Critical Action Department], CONTAINS(Criteria@row, @cell),
[Task Status Based on Due Date]:[Task Status Based on Due Date],
OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell),
CONTAINS("At Risk for Delay", @cell)))), "")

Bassam Khalil

@Katie Aldrich 

Done as per your request.

=IFERROR(COUNT(COLLECT([Critical Action Department]:[Critical Action Department],
[Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell),
[Task Status Based on Due Date *]:[Task Status Based on Due Date *],
OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))), "")