# Formula that counts if records meet specific criteria



## modestcode (Dec 30, 2022)

Hi, I am feeling a bit stumped on this excel formula I'm working on. The answer is probably easy.. If i can figure out a way to address this in a simplified example, then I can probably apply it to my more complex dataset. Essentially I'm trying to create a formula that only counts users with tickets in closed status. So in the example below, I would expect Frank to be the only user tied to Closed tickets, and my formula result to be 1. There are duplicate ticket IDs in my dataset but other fields make them unique records. The answer is probably easy, but any help would be greatly appreciated. Thanks!


Example of data:


* User**Ticket ID** Status*Bob1ClosedBob1ClosedBob2OpenBob3ClosedFrank4ClosedFrank5ClosedFrank6ClosedSteve7OpenSteve7Open


----------



## gravanoc (Dec 30, 2022)

If I understand you correctly l, you want to count the # of closed records for each person. To do this you can use COUNTIFS, where your criteria ranges are their names & the status columns, while the criteria will be each individual's names and "closed".  On my mobile so can't type up complete example at the moment


----------



## modestcode (Dec 30, 2022)

Good idea. So with this formula, my result is 2. Assuming the formula I have is counting Bob since there are 3 closed tickets. But I'd like to exclude Bob since he has 1 in open status.

{=SUM(IF(Table1[Status]="Closed", 1/COUNTIFS(Table1[Status],"Closed",Table1[User],Table1[User]),0))}


----------

