Help with COUNTIF formula

Jyarrow

Hi all,

I'm trying to finalize a formula - wondering if anyone can help?

I was successful with the first part of the formula which is this:

=COUNTIF([Departments Involved in Project]:[Departments Involved in Project], CONTAINS("IT", @cell))

The formula above tells me how many projects our IT dept is involved in. I'd like to drill down a bit more to know how many projects IT is involved in that are also department level. I don't know how to add an AND function. There is another column in the same sheet that I would like to reference, so if the column above contains "IT" and the second column is equal to "department level" I'd like to get the result of that. Any suggestions?

=COUNTIF([Departments Involved in Project]:[Departments Involved in Project], CONTAINS("IT", @cell, AND([CU Wide/Dept Level Project]:[CU Wide/Dept Level Project], CONTAINS("Department Level"))))

Paul Newcome

You would need a COUNTIFS (with the "S" on the end) instead to allow for multiple range/criteria sets.

https://help.smartsheet.com/function/countifs

Jyarrow

Thank you Paul for the response. I did try that, I'm getting stuck going from the first part of the formula to the second. Any suggestions?

Paul Newcome

It is just how it is outlined in the article.

=COUNTIFS(range 1, criteria 1, range 2, criteria 2)

Jyarrow

I saw that, here's where I'm at and am getting "unparseable"

=COUNTIFS([Departments Involved in Project]:[Departments Involved in Project], CONTAINS("IT"), [CU Wide/Dept Level Project]:[CU Wide/Dept Level Project], Department Level))

Paul Newcome

Looks like you are missing quotes around "Department Level" and have one too many closing parenthesis on the end.