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

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

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

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

--
jermsalerm
-----------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...fo&userid=3016
View this thread: http://www.excelforum.com/showthread.php?threadid=51629

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

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

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

--
jermsalerm
-----------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...fo&userid=3016
View this thread: http://www.excelforum.com/showthread.php?threadid=51629

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" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> 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
>

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

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 (D1010000).

'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

--
jermsalerm
-----------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...fo&userid=3016
View this thread: http://www.excelforum.com/showthread.php?threadid=51629