A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Counting based on the occurrence of text in a cell



 
 
Thread Tools Display Modes
  #1  
Old August 16th, 2004, 11:02 PM
smilroy
external usenet poster
 
Posts: n/a
Default Counting based on the occurrence of text in a cell

Hello,

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

I am looking for the occurrence aaaa.bbb.ccc in the following text and
I need them counted but also based on criteria in another field, as
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 those
which have text beyond aa.bb in the account. The aa.bb denotes an area
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 but
Excell does not seem to include the cells which have text beyond the
aa.bb (i.e aa.bb.cc.dd.ee, etc.)

Any help would be greatly appreciated.

Thanks,

Scot


---
Message posted from http://www.ExcelForum.com/

  #2  
Old August 16th, 2004, 11:51 PM
hgrove
external usenet poster
 
Posts: n/a
Default

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:B10 0="yes"))


---
Message posted from http://www.ExcelForum.com/

  #3  
Old August 17th, 2004, 12:54 AM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

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)

"hgrove " wrote in message
...
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:B10 0="yes"))


---
Message posted from http://www.ExcelForum.com/



  #4  
Old August 17th, 2004, 02:38 AM
RagDyer
external usenet poster
 
Posts: n/a
Default

Why is it necessary to include the second period?

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


--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"Bob Phillips" wrote in message
...
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)

"hgrove " wrote in message
...
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:B10 0="yes"))


---
Message posted from http://www.ExcelForum.com/



  #5  
Old August 17th, 2004, 02:20 PM
smilroy
external usenet poster
 
Posts: n/a
Default

Thanks a lot for your help. It works like a charm.

Regards,

Scot


---
Message posted from http://www.ExcelForum.com/

  #6  
Old August 17th, 2004, 02:35 PM
smilroy
external usenet poster
 
Posts: n/a
Default

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


---
Message posted from http://www.ExcelForum.com/

  #7  
Old August 17th, 2004, 04:28 PM
smilroy
external usenet poster
 
Posts: n/a
Default

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
---
Message posted from http://www.ExcelForum.com/

  #8  
Old August 17th, 2004, 04:46 PM
RagDyeR
external usenet poster
 
Posts: n/a
Default

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 !
--------------------------------------------------------------------


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


---
Message posted from http://www.ExcelForum.com/


  #9  
Old August 17th, 2004, 05:06 PM
RagDyeR
external usenet poster
 
Posts: n/a
Default

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!
==============================================


"smilroy " wrote in message
...
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
---
Message posted from http://www.ExcelForum.com/


  #10  
Old August 17th, 2004, 05:29 PM
smilroy
external usenet poster
 
Posts: n/a
Default

Thanks it works. When I moved the formula I made a mistake with the
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 the
same first column (i.e. CIBC.RM AND CIBC.TO.TS) and then add them
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 trouble
in thinking this through.



Any help is, as always, greatly appreciated.

Thanks,

Scot

Attachment filename: sumproduct_problem.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=651212
---
Message posted from http://www.ExcelForum.com/

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Word 97: Problem with Bottom Align Text in Cell Tables 1 June 3rd, 2004 10:19 PM
Do you have what it takes... Frank Kabel Worksheet Functions 1 February 22nd, 2004 09:30 PM
Counting cell values based on adjacent cell value over multiple columns h2oskier Worksheet Functions 2 February 19th, 2004 06:29 PM
Link or paste cells based on Date in cell Ayanna Worksheet Functions 0 December 16th, 2003 07:10 PM
Importing text to chart based on link to cell Stephen Charts and Charting 2 November 8th, 2003 06:07 AM


All times are GMT +1. The time now is 03:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.