Counting based on the occurrence of text in a cell

S

smilroy

Hello,

I need to add text based on the occurrence of text in string but
cannot discount the remainder of a string.

I am looking for the occurrence aaaa.bbb.ccc in the following text an
I need them counted but also based on criteria in another field, a
follows:

Column A Column B

aa.bb Yes
aa.bb.cc No
aa.bb.cc.dd Yes
aa.bb.cc.dd.ee Yes
aa.bb.cc.dd.ee.ff Yes
aa.bb.cc.dd.ee.ff.gg No
bc.dd.cc.ee.ff.gg.hhh
bc.ba.cc.ee.ff


I need to count all of the occurrences of aa.bb but also include thos
which have text beyond aa.bb in the account. The aa.bb denotes an are
of the company and get's more specific the longer the line of text is.
I am trying to add up the numbers of aa.bb where column b=yes bu
Excell does not seem to include the cells which have text beyond th
aa.bb (i.e aa.bb.cc.dd.ee, etc.)

Any help would be greatly appreciated.

Thanks,

Sco
 
H

hgrove

smilroy wrote...
...
I need to count all of the occurrences of aa.bb but also include
those which have text beyond aa.bb in the account. . . . I am
trying to add up the numbers of aa.bb where column b=yes but
Excell does not seem to include the cells which have text
beyond the aa.bb (i.e aa.bb.cc.dd.ee, etc.)

=SUMPRODUCT((LEFT(A1:A100&".",6)="aa.bb.")*(B1:B100="yes")
 
B

Bob Phillips

or if the aa.bb after the firts character is valid,

=SUMPRODUCT(--(ISNUMBER(SEARCH("aa.bb.",A1:A100&"."))),--(B1:B100="yes"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

RagDyer

Why is it necessary to include the second period?

=SUMPRODUCT((LEFT(A1:A100,5)="aa.bb")*(B1:B100="Yes"))


--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
or if the aa.bb after the firts character is valid,

=SUMPRODUCT(--(ISNUMBER(SEARCH("aa.bb.",A1:A100&"."))),--(B1:B100="yes"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

smilroy

Hi,

Sorry but none of these formulas worked. These will only return th
number of aa.bb that exist but it still does not include occurrences o
aa.bb.cc.dd.ee. Excel does not recognize aa.bb.cc.dd.ee as a
occurrence of aa.bb

I don't need this to be an exact match. I need it to identify th
anything that starts with aa.bb.

For example:

aa.bb is counted

aa.bb.cc should also be counted

as well as,

aa.bb.cc.dd.ee

etc...

Thanks,

Sco
 
S

smilroy

I am trying to add all occurrences of CIBC.RM in Column A includin
CIBC.RM.AMICUS and any other that occur. This also must satisfy th
criteria of yes in column C.

Please take a look and provide any assistance if you can.

Thanks,

Sco

Attachment filename: sumproduct_problem.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=65110
 
R

RagDyeR

My formula *does* work.

In fact, all the suggested formulas work.

BUT, you must realize, that when we make suggestions, we test them on data
and values that you have posted.

The only way that we could test your data, was to *key* it into the cells.

If you would create a small list, as you posted, and try all of our
formulas, you would see that they all do as you requested.

HOWEVER, I would bet that your data is *not* keyed into your data list, but
probably put there as the result of some formula, or perhaps even imported
from another program or the net.

Since you make no mention of how your cells are populated, we can only make
suggestions with the information that you give us.

NOW, how is your data list populated?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


Hi,

Sorry but none of these formulas worked. These will only return the
number of aa.bb that exist but it still does not include occurrences of
aa.bb.cc.dd.ee. Excel does not recognize aa.bb.cc.dd.ee as an
occurrence of aa.bb

I don't need this to be an exact match. I need it to identify the
anything that starts with aa.bb.

For example:

aa.bb is counted

aa.bb.cc should also be counted

as well as,

aa.bb.cc.dd.ee

etc...

Thanks,

Scot
 
R

RagDyeR

Without looking, but just changing the formula to your newly posted specs,
try this:

=SUMPRODUCT((LEFT(A1:A100,7)="cibc.rm")*(C1:C100="Yes"))

This was tested on data *keyed* into the cells !!!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I am trying to add all occurrences of CIBC.RM in Column A including
CIBC.RM.AMICUS and any other that occur. This also must satisfy the
criteria of yes in column C.

Please take a look and provide any assistance if you can.

Thanks,

Scot

Attachment filename: sumproduct_problem.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=651103
 
S

smilroy

Thanks it works. When I moved the formula I made a mistake with th
columns and it was looking at an empty column.

One more question:

If I wanted the formula to look for two different sets of text in th
same first column (i.e. CIBC.RM AND CIBC.TO.TS) and then add the
together using the Yes/No in column C as criteria? Is this possible
I've added an additional spreadsheet for your reference.

I have an incredible headache today and have been experiencing troubl
in thinking this through.



Any help is, as always, greatly appreciated.

Thanks,

Sco

Attachment filename: sumproduct_problem.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=65121
 
B

Bob Phillips

=SUMPRODUCT(((LEFT(A1:A100,7)="cibc.rm")+((LEFT(A1:A100,10)="cibc.to.ts")))*
(C1:C100="Yes"))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

My pleasure.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

hgrove

RagDyer wrote...
Why is it necessary to include the second period?

=SUMPRODUCT((LEFT(A1:A100,5)="aa.bb")*(B1:B100="Yes"))
...

Because aa.bbxyz in all likelihood shouldn't be considered an instanc
of aa.bb, in much the same way that 1000 shouldn't be considered a
instance of 100
 
R

RagDyer

I can't dispute your logic Harlan.
I just can't seem to exhibit your abliity to read between (beyond) the
lines.
--
Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

RagDyer wrote...
Why is it necessary to include the second period?

=SUMPRODUCT((LEFT(A1:A100,5)="aa.bb")*(B1:B100="Yes"))
...

Because aa.bbxyz in all likelihood shouldn't be considered an instance
of aa.bb, in much the same way that 1000 shouldn't be considered an
instance of 100.
 
H

Harlan Grove

RagDyer said:
I can't dispute your logic Harlan.
I just can't seem to exhibit your abliity to read between (beyond) the
lines.
....

No great perception, just memory. I've been bitten by this bug before.
 
R

RT

What were these formulas? I need to count the number of
yes's and no's in a column. I sure would appreciate any
help!
 
R

RagDyeR

How about posting some information on what you "have", and what you would
like to "Have", with descriptions of your sheet and the data in it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top