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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Thanks a lot for your help. It works like a charm.
Regards, Scot --- Message posted from http://www.ExcelForum.com/ |
#6
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |