| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
In Excel, How do i add Words together.
I need to find a formula that will allow me to add words in a column and only
the specific words i want. iEx. in a column i will have the words: Broker, Agent, Agent, Agent, Loan Officer, Agent, Loan Officer. I need the formula to add and total the number of Brokers and Agetns that are there and not the Loan Officers. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excel, How do i add Words together.
You can count the number of cells that match a specific string using countif:
=countif(a:a,"Broker") "Tom B" wrote: I need to find a formula that will allow me to add words in a column and only the specific words i want. iEx. in a column i will have the words: Broker, Agent, Agent, Agent, Loan Officer, Agent, Loan Officer. I need the formula to add and total the number of Brokers and Agetns that are there and not the Loan Officers. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excel, How do i add Words together.
Let's try this one piece at a time, then you can combine them later:
With your list in Cell A1. Count of Agent is... B1: Agent C1: =(LEN(UPPER(A1))-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),"")))/LEN(B1) Count of Broker is..... B2: Broker C2: =(LEN(UPPER(A2))-LEN(SUBSTITUTE(UPPER(A2),UPPER(B2),"")))/LEN(B2) Does that help? *********** Regards, Ron "Tom B" wrote: I need to find a formula that will allow me to add words in a column and only the specific words i want. iEx. in a column i will have the words: Broker, Agent, Agent, Agent, Loan Officer, Agent, Loan Officer. I need the formula to add and total the number of Brokers and Agetns that are there and not the Loan Officers. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Whoops...misunderstood the question
I calculated how many times a word was found in a single cell.
Like... A1: Broker, Agent, Agent, Agent, Loan Officer, Agent, Loan Officer. *********** Regards, Ron "Ron Coderre" wrote: Let's try this one piece at a time, then you can combine them later: With your list in Cell A1. Count of Agent is... B1: Agent C1: =(LEN(UPPER(A1))-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),"")))/LEN(B1) Count of Broker is..... B2: Broker C2: =(LEN(UPPER(A2))-LEN(SUBSTITUTE(UPPER(A2),UPPER(B2),"")))/LEN(B2) Does that help? *********** Regards, Ron "Tom B" wrote: I need to find a formula that will allow me to add words in a column and only the specific words i want. iEx. in a column i will have the words: Broker, Agent, Agent, Agent, Loan Officer, Agent, Loan Officer. I need the formula to add and total the number of Brokers and Agetns that are there and not the Loan Officers. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
See if this works
Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH(B1:B10,"Broker~agent"))) Adjust the range to suit your situation. Does that help? *********** Regards, Ron "Tom B" wrote: yes and no... i think you had it right the first time. each word is in a separate cell. sample below: Name Position email phone A B C D 1 Jim Smith Agent 12345 2 John Smith Broker 12345 3 Jane Smith Loan Officer 12345 4 Jake Smith Agent jake @email.com 12345 Total Agent/Broker 3 (this is what I need) I need it to add all the agents in the column and not add the 'Loan Officer' "Ron Coderre" wrote: I calculated how many times a word was found in a single cell. Like... A1: Broker, Agent, Agent, Agent, Loan Officer, Agent, Loan Officer. *********** Regards, Ron "Ron Coderre" wrote: Let's try this one piece at a time, then you can combine them later: With your list in Cell A1. Count of Agent is... B1: Agent C1: =(LEN(UPPER(A1))-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),"")))/LEN(B1) Count of Broker is..... B2: Broker C2: =(LEN(UPPER(A2))-LEN(SUBSTITUTE(UPPER(A2),UPPER(B2),"")))/LEN(B2) Does that help? *********** Regards, Ron "Tom B" wrote: I need to find a formula that will allow me to add words in a column and only the specific words i want. iEx. in a column i will have the words: Broker, Agent, Agent, Agent, Loan Officer, Agent, Loan Officer. I need the formula to add and total the number of Brokers and Agetns that are there and not the Loan Officers. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Value Errors with EXCEL XP not showing up in EXCEL 2000 | Links and Linking in Excel | |||
| How do I add header information to an Excel file? In other words,. | New Users to Excel | |||
| Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
| how do I add accents for foreign words when using excel? | Excel Discussion (Misc queries) | |||
| Parsing words in excel cells | Excel Worksheet Functions | |||
Linear Mode
