Count Rows in Sheet Meeting Certain Criteria


I'm trying to get a count of the number of rows in my sheet that meet a certain criteria and am getting an #INVALID OPERATION error.

Here is what I have so far:

=COUNTIF([Resident (Lookup)]:[Resident (Lookup)], AND([Resident (Lookup)]@row, Status:Status <> "Closed"))

What I'm trying to do is get a count of the number of rows for a resident that are not closed. We have three statuses, New, In Progress and Closed, and if a resident has more than one ticket that is New or In Progress I want to see the total number of open tickets that they have.

Example, Resident ABC has two closed tickets, two new tickets and one in progress ticket. I'd like the column to return 3 for every row where Resident (Lookup) = ABC.

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!