sumif questions

[BAS]

I would like to create a function to sum my wins (W) and Losses (L) and
calculate winning percentage. So, if I have a column (J6:J40) that is a mix
of W's and L's.

I would like to sum: wins in cell K6, losses in cell k7, and winning
percentage in cell k8.

Is the sumif even the right function?

 

[BAS]

Adding another criteria. Each win or loss will also be classified as these
designations: S, O, U, D, or P. So I'd like the function to include this
column (H6:H40), which will allow me to calculate wins/losses by category
found in column H.

 

[BAS]

Adding an example...

Column H Column J Column K
Category Wins/Losses Category S
S W Wins 3
S W Losses 3
S L Percentage 0.500
S W
S L
S L Category O
O W Wins 3
O W Losses 0
O W Percentage 1.000
U L
U L
U W
D L
D W
D L
D W
D W
P L
P W
P W
P W

 

[Dave Peterson]

I think I'd make a little table.

I'd put S, O, U, D, P in a column (say X6:X10).

Then to count the number of wins for X6 (S)

I'd put this in y6:
=sumproduct(--($h$6:$h$40=$x6),--($J$6:$J$40="W"))
then in z6 to count the number of losses:
=sumproduct(--($h$6:$h$40=$x6),--($J$6:$J$40="L"))
Then in AA6 the percentage
=if(sum(x6:z6)=0,"",y6/(y6+z6))
(give this a nice number format)

And these three formulas down.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html