Countif and formula

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)))), "")

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)))), "")

All comments

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

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

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.

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 )

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

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.

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.

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

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.

@Bassam Khalil I have shared a sample sheet.

Hi @Katie Aldrich

I fix it please check it.

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)))), "")