CountIf

[Mike]

Hello all,
Using Excel XP.

I have a formula in column Q that gives an overall running win and lost
record based on either a W (for a win) or an L (for a lost) in column P.

An example in cell P4 is a W so the formula would give a result of 1-0
P5 is an L so the formula would give a
result of 1-1
P6 is an L so the formula would give a
result of 1-2

=COUNTIF($P$4:P4,"W")&"-"& COUNTIF($P$4:P4,"L")
=COUNTIF($P$4:P5,"W")&"-"& COUNTIF($P$4:P5,"L")
=COUNTIF($P$4:P6,"W")&"-"& COUNTIF($P$4:P6,"L")

How could write the above formula based on a ceertain location in column B.
I would like to find out the home win and lost record if the location in
column B is equal to Boston. I tried adding another countif but it still
gave me the overall record. Any help would be appreciated.

Mike

[duane]

try

=SUMPRODUCT(($P3:P$3="W")*($b3:b$3="boston"))&"-"& SUMPRODUCT(($P3:P$3="L")*($b3:b$3="boston"))

in q3 and copy down

[Lewis Clark]

Try the Conditional Sum Wizard under the tools menu. That will let your
formula only execute if colum B = Boston.

"Mike" <[email protected]> wrote in message
news:TQC9e.17202$B93.1829@lakeread06...
>
> Hello all,
> Using Excel XP.
>
> I have a formula in column Q that gives an overall running win and lost
> record based on either a W (for a win) or an L (for a lost) in column P.
>
> An example in cell P4 is a W so the formula would give a result of
> 1-0
> P5 is an L so the formula would give a
> result of 1-1
> P6 is an L so the formula would give a
> result of 1-2
>
> =COUNTIF($P$4:P4,"W")&"-"& COUNTIF($P$4:P4,"L")
> =COUNTIF($P$4:P5,"W")&"-"& COUNTIF($P$4:P5,"L")
> =COUNTIF($P$4:P6,"W")&"-"& COUNTIF($P$4:P6,"L")
>
> How could write the above formula based on a ceertain location in column
> B. I would like to find out the home win and lost record if the location
> in column B is equal to Boston. I tried adding another countif but it
> still gave me the overall record. Any help would be appreciated.
>
> Mike
>