sumif questions

  • Thread starter Thread starter BAS
  • Start date Start date
B

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?
 
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.
 
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
 
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
 
Back
Top