In Excel, How do i add Words together.

[Tom B]

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.

 

[bpeltzer]

You can count the number of cells that match a specific string using countif:
=countif(a:a,"Broker")

 

[Ron Coderre]

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

 

[Ron Coderre]

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

 

[Tom B]

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 (e-mail address removed) 12345
2 John Smith Broker (e-mail address removed) 12345
3 Jane Smith Loan Officer (e-mail address removed) 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]

Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH(B1:B10,"Broker~agent")))

Adjust the range to suit your situation.

Does that help?

***********
Regards,
Ron