|
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. |
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. |
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. |
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. |
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. |
| All times are GMT +1. The time now is 10:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com