Vlookup + Countif

[DaFonz01]

Hi!

I am trying to do a vlookup in another worksheet and instead of giving me just 1 result, I would like it to count how many results meet the criteria.


Here is my example:

Sheet 1:
A1: ID # B2: Status
A2: 123 B2: Green
A3: 123 B2: Red
A4: 123 B3: Green

I need to lookup the ID# 123 and count how many times it was Green on another worksheet.

Thanks for your help!!!!

 

[rob.barnes01]

=COUNTIFS(A:A,"123",B:B,"Green")

 

[DaFonz01]

Thanks Rob, but I forgot to tell you that my work only has Excel 2003 and can not use the =countifs formula.

 

[Jonmo1]

Try

=SUMPRODUCT(--(A1:A1000=123),--(B1:B1000="Green"))

 

[DaFonz01]

Worked like a charm. Thanks!