Lookup question

[mhoss24]

Using XL 2003

Im trying to determine how many Nos are tied to a certain subject.

Here is the format:

_| Subject | Y/N |
1 SUB1 YES
2 SUB2 YES
3 SUB1 NO
4 SUB1 YES
5 SUB2 NO


I have a dropdown on a different sheet where I can select a subject. I want to be able to determine how many nos pertain to each subject.
Example: When I select SUB1 from my dropdown, I want it to print out "1". Only problem is I have about 50 rows of yes/nos for about 10 different subjects. For a small sample I could use [[given that 'Sheet2'!$A$1 is my dropdown]]

=(IF(AND(A1='Sheet2'!$A$1, B1="NO"),1,0)+(A2='Sheet2'!$A$1, B2="NO"),1,0)+(A3='Sheet2'!$A$1, B3="NO"),1,0)+(A4='Sheet2'!$A$1, B4="NO"),1,0)+(A5='Sheet2'!$A$1, B5="NO"),1,0))



That's a really ugly way out, but the only thing that comes to my head for the moment and it will not work for a large sample like I have. Someone call me an idiot and tell me how to do this.

Please let me know if you need more information. Thanks in advance!!!

 

[njimack]

=SUMPRODUCT(--(A1:A50=Sheet2!A1),--(B1:B50="NO"))

 

[mhoss24]

Thanks alot! I tried that before but I was selecting the entire A and B column. It spit back a "number error". When I selected only the cells, it works. Weird to me... You are of great help!

Best Regards,
Matthew