Excel 2007 : Total Counts of occurances for specific dates

**BobBing** · 12-29-2011, 12:33 PM

Hi guys,

I am trying to figure out how to get a specific count of opportunities that are turned into a win/loss ratio over three years, pulling the data out for each year by a sales rep.
So for example I may have Ray, Bob, and Steve's opportunities over 2009, 2010 and 2011. In that data it shows the opportunity as either open, won or lost.
A B C
Ray 2009 Won
Ray 2010 Loss
Ray 2011 Open
Bob 2009 Won
Bob 2010 Won
Bob 2010 Open
Jon 2011 Loss
Jon 2009 Open
Jon 2009 Loss

I have the Countifs(A1:C9,A1) To count totals for Ray, and so forth for each rep. How can I write it so that I can get a count of just wins, and also get total counts for just 2009 and also just wins for 2009?
I realize the ability to filter and do the counts but I am trying to figure it out so I can dump data and it will automatically populate my other sheet.
I've attached a small spreadsheet as an example.

Any help would be greatly appreciated!

**Andrew-R** · 12-29-2011, 01:09 PM

To get the total number of opportunities for a rep in a given year use:

=SUMPRODUCT(--($A$2:$A$11=A2),--(YEAR($B$2:$B$11)=YEAR(B2)))

And to get the number of wins for a year try:

=SUMPRODUCT(--($A$2:$A$11=A2),--(YEAR($B$2:$B$11)=YEAR(B2)),--($C$2:$C$11="Won"))

Edited to add: For number of wins in total you can stick with COUNTIFS* ... =COUNTIFS($A$2:$A$11,A2,$C$2:$C$11,"Won")

* Although, as it stands, your sheet need only use COUNTIF, not COUNTIFS