Formula Help

[jeefreak]

Hi,

I have a table with a list of names and account status. Names might show up multiple times due to multiple accounts. I want to try and obtain the number of persons with all accounts closed.

Name | Status

PersonA | Closed
PersonA | Open
PersonA | Closed
PersonB | Closed
PersonB | Closed
PersonC | Open
PersonD | Closed

In the example above, it should come up with 2 (PersonB and PersonD). I can only think of complicated and manual procedures to do this. Is there a simple formula that would calculate this?

 

[shg]

      ---A--- --B--- ----C-----
  1    Name   Status           
  2   PersonA Closed           
  3   PersonA Open             
  4   PersonA Closed           
  5   PersonB Closed All closed
  6   PersonB Closed All closed
  7   PersonC Open             
  8   PersonD Closed All closed

In C2 and down,

=IF(COUNTIFS($A$2:$A$8, A2, $B$2:$B$8, "<>Closed"), "", "All closed")

 

[Marcelo Branco]

Maybe this Array-formula

=SUM(IF(COUNTIF(OFFSET($A$2,,,ROW($A$2:$A$8)-ROW($A$2)+1,1),$A$2:$A$8)=1,IF(COUNTIF($A$2:$A$8,$A$2:$A$8)=COUNTIFS($A$2:$A$8,$A$2:$A$8,$B$2:$B$8,"Closed"),1)))

Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

M.