sumif questions
 

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?

sumif questions
 

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" wrote:

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?

sumif questions
 

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


"BAS" wrote:

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" wrote:

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?

sumif questions
 

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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


BAS wrote:

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

"BAS" wrote:

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" wrote:

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?


--

Dave Peterson