Counting cells not containing specific text

Hi,

What I'm trying to do is get a count of cells from one column based on
cells that do not contain specific text in another column, i.e.;

A B
1 at Laurel W
2 Silver Spring L
3 Wheaton W
4 at Rockville W
5 at Bethesda L
6 Olney L
7 Gaithersburg W
8 Takoma Park W
9 at Chevy Chase W

I am trying to get a count of W's and L's based on home games, without
the "at". I have a formula for away games but can't figure out the
home games.

Any help would be greatly appreciated.

Thanks, DB

Hi

One way
=SUMPRODUCT(--(LEFT(B2:B100="at"),--(RIGHT(B2:B100="W"))
for Away Wins.
Obviously, change the last part to "L" for Losses.

Alternatively, place the W or L in cell C1, then the single formula
=SUMPRODUCT(--(LEFT(B2:B100="at"),--(RIGHT(B2:B100=C1))

--
Regards

Roger Govier


<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> What I'm trying to do is get a count of cells from one column based on
> cells that do not contain specific text in another column, i.e.;
>
> A B
> 1 at Laurel W
> 2 Silver Spring L
> 3 Wheaton W
> 4 at Rockville W
> 5 at Bethesda L
> 6 Olney L
> 7 Gaithersburg W
> 8 Takoma Park W
> 9 at Chevy Chase W
>
> I am trying to get a count of W's and L's based on home games, without
> the "at". I have a formula for away games but can't figure out the
> home games.
>
> Any help would be greatly appreciated.
>
> Thanks, DB
>

Try this:
For At-Home Wins: =SUMPRODUCT((LEFT(A1:A9,3)="at ")*(B1:B9="W"))
For At-Home Losses: =SUMPRODUCT((LEFT(A1:A9,3)="at ")*(B1:B9="L"))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"[email protected]" wrote:

> Hi,
>
> What I'm trying to do is get a count of cells from one column based on
> cells that do not contain specific text in another column, i.e.;
>
> A B
> 1 at Laurel W
> 2 Silver Spring L
> 3 Wheaton W
> 4 at Rockville W
> 5 at Bethesda L
> 6 Olney L
> 7 Gaithersburg W
> 8 Takoma Park W
> 9 at Chevy Chase W
>
> I am trying to get a count of W's and L's based on home games, without
> the "at". I have a formula for away games but can't figure out the
> home games.
>
> Any help would be greatly appreciated.
>
> Thanks, DB
>
>

Having seen Ron's answer, I can see that I rather foolishly assumed all
the data was in column A with an entry like

"at Laurel W".
I'm sure Ron's interpretation is more likely to be correct.

--
Regards

Roger Govier


"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> One way
> =SUMPRODUCT(--(LEFT(B2:B100="at"),--(RIGHT(B2:B100="W"))
> for Away Wins.
> Obviously, change the last part to "L" for Losses.
>
> Alternatively, place the W or L in cell C1, then the single formula
> =SUMPRODUCT(--(LEFT(B2:B100="at"),--(RIGHT(B2:B100=C1))
>
> --
> Regards
>
> Roger Govier
>
>
> <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> What I'm trying to do is get a count of cells from one column based
>> on
>> cells that do not contain specific text in another column, i.e.;
>>
>> A B
>> 1 at Laurel W
>> 2 Silver Spring L
>> 3 Wheaton W
>> 4 at Rockville W
>> 5 at Bethesda L
>> 6 Olney L
>> 7 Gaithersburg W
>> 8 Takoma Park W
>> 9 at Chevy Chase W
>>
>> I am trying to get a count of W's and L's based on home games,
>> without
>> the "at". I have a formula for away games but can't figure out the
>> home games.
>>
>> Any help would be greatly appreciated.
>>
>> Thanks, DB
>>
>
>

Hopefully, it was obvious that I meant to type Away, instead of At-Home.

(I hope my fingers are still under warranty)

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

> Try this:
> For At-Home Wins: =SUMPRODUCT((LEFT(A1:A9,3)="at ")*(B1:B9="W"))
> For At-Home Losses: =SUMPRODUCT((LEFT(A1:A9,3)="at ")*(B1:B9="L"))
>
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "[email protected]" wrote:
>
> > Hi,
> >
> > What I'm trying to do is get a count of cells from one column based on
> > cells that do not contain specific text in another column, i.e.;
> >
> > A B
> > 1 at Laurel W
> > 2 Silver Spring L
> > 3 Wheaton W
> > 4 at Rockville W
> > 5 at Bethesda L
> > 6 Olney L
> > 7 Gaithersburg W
> > 8 Takoma Park W
> > 9 at Chevy Chase W
> >
> > I am trying to get a count of W's and L's based on home games, without
> > the "at". I have a formula for away games but can't figure out the
> > home games.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks, DB
> >
> >