Added up # of cells containing a specified word
 

In cell A1 I would like to have a formula that indicates the number of
times the word "Yellow Pages" appears in cells C10:C1000. The C column
inidicates a referral source. So some of the cells will have "Yellow
Pages" and some wont. In cell A1 I would like to have it tell me the
total # of referrals from the lead source "Yellow Pages". Any
suggestions?


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=516297

Added up # of cells containing a specified word
 

See if this works for you:

A1: COUNTIF(C10:C1000,"*yellow pages*")

That will count any cell that includes "yellow pages"

Examples that qualify:
From yellow pages
newspaper and yellow pages
yellow pages
etc

Does that help?

Regards,
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=516297

Added up # of cells containing a specified word
 

Try this:

=COUNTIF(C10:C1000,"Yellow Pages")

HTH,
Elkar


"jermsalerms" wrote:


In cell A1 I would like to have a formula that indicates the number of
times the word "Yellow Pages" appears in cells C10:C1000. The C column
inidicates a referral source. So some of the cells will have "Yellow
Pages" and some wont. In cell A1 I would like to have it tell me the
total # of referrals from the lead source "Yellow Pages". Any
suggestions?


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=516297

Added up # of cells containing a specified word
 

what function would return the number of rows that contains "Yellow
Pages" in column A and "Interested" in column B.

So if both were not present the row would not be counted in the
results.


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=516297

Added up # of cells containing a specified word
 

Try this:

Where "yellow pages" could be in F1:F20 and "interested" could be in
H1:H20:

This formula counts instances where cells in Col_F equal "yellow pages"
and the corresponding cells in Col_H equal "interested":
A1: =SUMPRODUCT((F1:F20="yellow pages")*(H1:H20="interested"))

Are we there yet?

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=516297

Added up # of cells containing a specified word
 

I have a database being exported to a spreadsheet with over 40 fields
(columns) and 1000+ clients (rows). One of the fields (column C)
incidates that referral source. I would like to copy & paste the list
into sheet 1 and have 5 seperate sheets for each of the 5 different
referral sources. So that when I go to sheet 2 the only thing I will
see are the clients and their 40 associated fields that are referred
(column C) by say "John Smith"


Is there a function or script that will search column C for "John
Smith" and then fill in all the 40 fields on sheet 2.


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=516297

Added up # of cells containing a specified word
 

I just realized that isnt working. The result is #NAME?. Any suggestions


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=516297

Added up # of cells containing a specified word
 

First of all is to use CountIF function.
The easiest way get the result you want is to make a simple Pivot table.
If you need the list for specific data just double click "grand total" in
the pivot and it will generate new list with only those values. You can do
the additional calculations then if needed

BR

Kruno



"jermsalerms"
wrote in message
...

I just realized that isnt working. The result is #NAME?. Any suggestions


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile:
http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=516297

Added up # of cells containing a specified word
 

Jermsalerms:

Usually the #NAME! error means you are trying to use a function in the
Analysis ToolPak without the add-in loaded. But since the formula I
posted doesn't include any of those functions, you probably have a typo
in your formula.

Regards,
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=516297

Added up # of cells containing a specified word
 

I am not sure what you mean by pivot table.

I have two sheets....

'Sheet One' with client data...this is the sheet that has the referral
source (C10:C1000) and the status fields (D10:D10000).

'Sheet Two'...I want to list statistics....so on this sheet I may
list:

Cell A1 = the # of clients that have a referral source of "Yellow
Pages" and a status of "Interested"
Cell A2 = the # of clients that have a referral source of "Yellow
Pages" and a status of "Not Interested"
cells A3:A15 will contain other variations of statistical data.

If this Pivot table will accomplish this can you explain it more to me?


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=516297

Added up # of cells containing a specified word
 

I tried your formula on a dummy spreadsheet and it worked so I will go
back and try and figure out my mistake on the real spreadsheet. thanks


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=516297

Added up # of cells containing a specified word
 

Thanks. I figured it out...you were right...a typo in the formula


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=516297

Added up # of cells containing a specified word
 

You're very welcome....I'm glad you figured it out.

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=516297