Count Rows in Sheet Meeting Certain Criteria

Bethany Garcia

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.

Mike TV

@Bethany Garcia

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

Use COUNTIFS and specify the first range and it's criteria and then the 2nd range and it's criteria.

Bethany Garcia

Thank you, this is what I needed!

Mike TV

@Bethany Garcia

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

Use COUNTIFS and specify the first range and it's criteria and then the 2nd range and it's criteria.

Bethany Garcia

Thank you, this is what I needed!