Formula that counts if records meet specific criteria

[modestcode]

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
Bob	1	Closed
Bob	1	Closed
Bob	2	Open
Bob	3	Closed
Frank	4	Closed
Frank	5	Closed
Frank	6	Closed
Steve	7	Open
Steve	7	Open

 

[gravanoc]

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]

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