urgent help please! year statistics with excel formula

[mavi99]

Is it possible to create year statistics with excel formula for below:

Think that I have a list as below on an excel work sheet:

01.02.2010 win
03.04.2010 fail
03.05.2010 win
04.08.2010 win
02.02.2011 win
02.05.2012 win
02.08.2012 fail
02.10.2013 win

now, I want to write a formula (not with filtering) with excel (count if??) to see how many times I won for year 2010, 2011, 2012, 2013.
How can I do that?


I know that if A column was created only with years, I can write COUNTIFS(A1:A8;"2010";B1:B8;"win") for how many times I won for 2010 but when A column is created with dates like above, how should I write the formula?

Thank you in advance.

Onur

[Ace_XL]

Try

=SUMPRODUCT((YEAR(A1:A28)=2010)*(b1:b28="win"))

[mavi99]

Try

=SUMPRODUCT((YEAR(A1:A28)=2010)*(b1:b28="win"))

Actually it works, Ace!
Then would you be so kind enough to help me with these?

1. Let's say that there is another text in B column as "tie". how should I change it when I need to know how many times I won or tied for year 2010?
2. How can I count the years? (how many 2010s are there?)

[Tony Valko]

For wins or ties...

=SUMPRODUCT(--(YEAR(A1:A8)=2010),--ISNUMBER(MATCH(B1:B8,{"Win","Tie"},0)))

To count just the year 2010...

=SUMPRODUCT(--(YEAR(A1:A8)=2010))

[mavi99]

For wins or ties...

=SUMPRODUCT(--(YEAR(A1:A8)=2010),--ISNUMBER(MATCH(B1:B8,{"Win","Tie"},0)))

To count just the year 2010...

=SUMPRODUCT(--(YEAR(A1:A8)=2010))

Tony,
Thank you very much. You are great.

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.