Function SUMPRODUCT (or COUNTIFS) for 2 or more options?

[kasanoff]

Hi guys!

I'm using Excel 2003 and for instance I have a following table:

A B
1 2008 W
2 2008 W
3 2008 M
4 2009 M

I'd like to know how many 2008 year is in the column A which has
letter "W" for example. The correct answer is 2 and can get this with
this function:

=SUMPRODUCT(--(A1:A4=2008),--(B1:B4="W"))

The function COUNTIFS works only in 2007

So that's okay. But now I'm changing the B column to following:

A B
1 2008 W1
2 2008 W2
3 2008 M1
4 2009 M2

And now I'd like to know how many 2008 year is in the column A which
has "W1" OR "W2". The correct answer is also 2 but how can I count
that?

I am kindly grateful for the anwer!

Thanks!

Regards

 

[Wild Bill]

A very minor tweak handles that:
=SUMPRODUCT(--(A1:A4=2008),--(LEFT(B1:B4,1)="W"))

 

[kasanoff]

Thanks so much, it works

Regards