How to plot COUNTIF for multiple criterias ???? Lets find out...

Hi All,


Recently, I had to struck my head on my desk trying out for plotting a "Countif" Formula with multiple criterias. I invested almost 2-3 hrs in office googling for the same.. Nothing was the result !!!!


Then, Somehow I could recollect my Excel formula encyclopedia in my mind and used "SUMPRODUCT" to get the result.


Wanna know How do I do it ...? Let me explain ...


I have below table out of which I wasnted to derive 2 things :
  Q1. How many employees have a valid Passport and Valid Visa ?
  Q2. Q . How many employees do have a valid Passport but don’t have a Valid Visa ?


Obviously, data being so small , I can just figure it out by looking at it ... but when data is huge, I need to use SUMPRODUCT...(as given in screenshot below :

Employee	Has an passport ?	Has an valid Visa ??
A	YES	NO
B	NO	NO
C	YES	YES
D	YES	NO

so, my answers would be below formulas :

Q1. =SUMPRODUCT(--($B$2:$B$5="YES"),--($C$2:$C$5="YES"))

Q2 .=SUMPRODUCT(--($B$2:$B$5="YES"),--($C$2:$C$5="NO"))

So, the formula is :

=SUMPRODUCT(--(Range,Criteria),--(Range,Criteria),.....)

IMPORTANT :
DO NOT FORGET "--" (Double hyphen) AS IT PLAYS A KEY ROLE IN ARRAY FORMULAS.

Check and let me know, guys....

Keep enjoying the posts....