Excel Pivot count occurances under condition

Question

In excel, and preferably using pivot tables, I want to count the number of occurrences that have a specific value for different line items.

---

Tom | yes
Tom | no
Max | n/r
Max | yes
Max | yes
Max | no

---

The pivot table should have two line items (Max/Tom), one column that counts the sum of occurrences of "yes" and "no" and one column that counts only "yes". The result would be that I can say "Max has won 2 out of 3 relevant cases; Tom has won 1 out of 2 relevant cases"

I know how to do it with formulas, but was wondering if using pivot-tables is also a possibility.

Answer 1

If you can add helper columns I would add two, one for yes and one for no. In the yes column (assuming your data starts in cell A1), in C2 type =--(B2="yes") then in D2 type =--(B2="no")

This will return a 1 if the cell is yes or no like follows:

Name    Data   Yes  No
Tom     yes     1    0
Tom     no      0    1
Max     n/r     0    0
Max     yes     1    0
Max     yes     1    0
Max     no      0    1

Then create a pivot table, put Name as Row Labels, then in Values have Sum of Yes and Sum of No

http://oi63.tinypic.com/10z5j55.jpg

Answer 2

Additional fields are not necessary. When creating the pivot table, set Name equal to your Row value and Data for your Columns value. Then, drag the Name field into the Values area and voila! Screenshot attached.