count text in two cells

[cadking]

I have a list of my High schools football game in one column and in another column I have WON or LOST I know I can use =COUNTIF(F4:F305,"Breckinridge*") to count the the number of time they have played each team or the grand total number of wins and lost but is there a way to match each team up with WON and LOST column and count that and get the win and lost record of each team

1993
WON Breckinridge Co.
WON LaRue County
WON Grayson County*
WON Marion County*
WON Central Hardin
LOST Nelson County*
LOST Elizabethtown
WON Barren County*
WON North Hardin
WON North Bullitt*
LOST Woodford County
1994
WON Breckinridge Co.
WON LaRue County
WON Grayson County*
WON Marion County*
LOST Central Hardin
WON Nelson County**
LOST Elizabethtown
WON Barren County*
LOST North Hardin
WON North Bullitt*
WON Woodford County
WON South Oldham
LOST Lincoln County

[hemesh]

Are you looking for this





if answers helped click" * " add rep icon in the bottom left corner of my post

[oeldere]

With an pivot table.

See the result.

Aantal van choise Kolomlabels
Rijlabels 1993 1994 Eindtotaal
Barren County* 1 1 2
Breckinridge Co. 1 1 2
Central Hardin 1 1 2
Elizabethtown 1 1 2
Grayson County* 1 1 2
LaRue County 1 1 2
Lincoln County 1 1
Marion County* 1 1 2
Nelson County* 1 1
Nelson County** 1 1
North Bullitt* 1 1 2
North Hardin 1 1 2
South Oldham 1 1
Woodford County 1 1 2
Eindtotaal 11 13 24

[cadking]

It did help a little the * after the teams name is overtime on the countif function =COUNTIF(F4:F305,"Breckinridge*") if you put a * after the word it looks for a words that has Breckinridge at the begin of it and some text after it is there a way to do it on SUMPRODUCT

[Tony Valko]

One way...

A4 = Breckinridge

=SUMPRODUCT(--ISNUMBER(SEARCH(A4,F4:F305)))

[hemesh]

you can use =SUMPRODUCT((ISNUMBER(SEARCH($D2,$A$2:$A$26$))*($B$2:$B$26=E$1)))

with this in d2 you can keep wild card characters

[cadking]

thank you this is what I wanted
=SUMPRODUCT((ISNUMBER(SEARCH(D13,Sheet2!F4:F664))*(Sheet2!E4:E664=E1)))
and you helped a lot

[Tony Valko]

=SUMPRODUCT(--ISNUMBER(SEARCH(D13,Sheet2!F4:F664)),--(Sheet2!E4:E664=E1))

For more info on SUMPRODUCT see this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

[hemesh]

Hello Cadking if answer resolves your query than mark the thread as solved go to thread tools mark as solved


if answer helped you click " * "add rep icon in the bottom left corner of my post