Added up # of cells containing a specified word

J

jermsalerms

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?
 
R

Ron Coderre

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
 
J

jermsalerms

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

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

Ron Coderre

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
 
J

jermsalerms

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.
 
K

KrunoG

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" <[email protected]>
wrote in message
news:[email protected]...
 
R

Ron Coderre

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
 
J

jermsalerms

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 referra
source (C10:C1000) and the status fields (D10:D10000).

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

Cell A1 = the # of clients that have a referral source of "Yello
Pages" and a status of "Interested"
Cell A2 = the # of clients that have a referral source of "Yello
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
 
J

jermsalerms

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
 
Top