Combining two formulae

[Nikki]

Hi Chandoo and Co!


First of all thank you for this amazing forum - I have learnt so much as a result of your generosity - you are all awesome!


I am struggling to combine two formulae - I want to determine the number of unigue personnel numbers using two criteria - are you able to help please?


I have two formulae but i just can't manage to combine them successfully :


=SUMPRODUCT(1/COUNTIF(Sheet2!A2:A7,Sheet2!A2:A7))

=COUNTIFS(Sheet2!B2:B7,"February 2012",Sheet2!C2:C7,"Completed")


Column A Column B Column C

Pers No Training Month Pre/Can/Comp

20052 February 2012 Completed

20052 February 2012 Cancelled

20052 June 2012 Completed

20788 February 2012 Completed

20788 June 2012 Completed

21067 January 2012 Completed


Thank you so much and once again thank you for providing this forum for our problems.


Much obliged, Nikki, South Africa

 

[Debraj]

Hi Nikki..


* Assuming "February 2012" is text.. If not please let us know..

* I know you have Excel 2007 (or higher) as you have already used COUNTIFS..

{=SUM(IF(("Completed"=$C$2:$C$7)*("February 2012"=$B$2:$B$7),1/COUNTIFS($C$2:$C$7,"Completed",$B$2:$B$7,"February 2012",$A$2:$A$7,$A$2:$A$7)),0)}

Ctrl + Shift + Enter


PS: Sorry.. I never worked for any Narcotics or Forensic Department..

Hope someone will EVALUATE the formula.. Please..

 

[Nikki]

Thanks so much, Debraj! February 2012 is not actually text but I will test it anyway to see if it works.


Your help is much appreciated, take care, Nikki