Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Summing specific texts
Hi, please help me, I need assistance: I am trying to find the total number of times a specific word has been entered in a column. I tried using LEN and SUBSTITUE but it seems like that only works if it's a single letter? Here's the details - some one much smarter than I am will surely have an answer for me? Word is *Toffee * (and is written in A1) The field is *D1 to D600 *(a text formatted column containing single words in each cell) -I have had some success with the LEN and SUBSTITUTE formula when using a single letter but I have had to write out the whole field as D1&D2&D3&D4&D5... etc, it wouldn't work using D1:D10) - I dont want to write 1 to 600 with & between each entry - is there another way?- The Total should be supplied in *B1* (in number format) Look forward to hearing your response Cheers LOSTLADY! -- LostLady ------------------------------------------------------------------------ LostLady's Profile: http://www.excelforum.com/member.php...o&userid=23447 View this thread: http://www.excelforum.com/showthread...hreadid=377747 |
#2
|
|||
|
|||
In cell b1 format to number then use =countif(d1:d600,"toffee")
"LostLady" wrote: Hi, please help me, I need assistance: I am trying to find the total number of times a specific word has been entered in a column. I tried using LEN and SUBSTITUE but it seems like that only works if it's a single letter? Here's the details - some one much smarter than I am will surely have an answer for me? Word is *Toffee * (and is written in A1) The field is *D1 to D600 *(a text formatted column containing single words in each cell) -I have had some success with the LEN and SUBSTITUTE formula when using a single letter but I have had to write out the whole field as D1&D2&D3&D4&D5... etc, it wouldn't work using D1:D10) - I dont want to write 1 to 600 with & between each entry - is there another way?- The Total should be supplied in *B1* (in number format) Look forward to hearing your response Cheers LOSTLADY! -- LostLady ------------------------------------------------------------------------ LostLady's Profile: http://www.excelforum.com/member.php...o&userid=23447 View this thread: http://www.excelforum.com/showthread...hreadid=377747 |
#3
|
|||
|
|||
LostLady: We don't want you to be lost for too long. Use COUNTIF with a 'wildcard' to find your search word in a range, as such: In B1 place this formula: =COUNTIF(D1:D600,"*TOFFEE*") --- This will find all cases of ToFfeE in your range, such as "Toffee Crisp", "Ground TOFFEE", "nuts toffee coated" will return 3 If you want to search for the contents of A1, then use: =COUNTIF(D1:D600,"*"&A1&"*") Does this work for you? Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=377747 |
#4
|
|||
|
|||
If the word "Toffee" can appear more than once in a cell and you want to know how many times Toffee appears in a range of cells Ttry this: =SUMPRODUCT(LEN(D1:D600)-LEN(SUBSTITUTE(UPPER(D1:D600),UPPER(A1),"")))/LEN(A1) Example: A1: Toffee and donuts A2: Toffee is not Toffee-free. Count of Toffee is 3. Note: SUBSTITUTE is case sensitive, hence the UPPER function. I hope that helps. Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=377747 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight a row if a specific cell is specific numbers/words | Excel Worksheet Functions | |||
Number of records by Month that meet a specific requirement | Excel Worksheet Functions | |||
How do I make a cell date specific to input a value on a specific. | Excel Discussion (Misc queries) | |||
How do I insert an image into a specific cell within a protected . | Excel Discussion (Misc queries) | |||
Hyperlink to specific sheet in Excel Web File | Links and Linking in Excel |