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

Counting cells not containing specific text
 

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


wrote in message
oups.com...
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

Counting cells not containing specific text
 

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


" 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

Counting cells not containing specific text
 

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" wrote in message
...
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


wrote in message
oups.com...
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

Counting cells not containing specific text
 

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


" 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