formula needed

Hope I can clarify. Here is an example of what I'm working with. Sheet
One is Raw Data dumped in from a seperate report. Sheet 2 is where I'm
doing all my work. Sheet One can not be changed around because it is
used by other calculations. Also, Sheet One has 3 columns where column
B is unneeded information. No Cell will actually contain a number. It
is all Text except for the desired result.
SHEET 1
Customer 1 Product 1
Product 1
Product 1
Customer 2 Product 2
Product 2
Product 2
Customer 3 Product 1
Product 1
Product 1
SHEET 2
Product 1 =2
Product 2 =1
Product 3 =


I want the formula, for example, to search for Product 1 (to count the
number of customers using a specific product) on sheet One. When seeing
product 1 in Column C, I want it to check Column A for text. If A is
populated, then count, so the result for this example would be Product
1 = 2. The products are listed on Sheet 2 where the result is to be
posted.

JulieD Wrote:
> Hi Darrell
>
> i'm going to assume you meant, count the number in column A of sheet1
> as
> long as the value in column C of sheet 1 is not blank
> formula on Sheet2 is (where the value you're looking up is in cell A2)
>
> =SUMPRODUCT(--(Sheet1!$A$1:$A$100=A2),--(Sheet1!$C$1:$C$100""))
> check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> for details on the sumproduct function
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Darrell" [email protected] wrote in message
> news:[email protected]
>
> Hello,
> I hope I can explaine this well enough. I am using two worksheets.
> Worksheet 1 is an imported file, worksheet 2 is where all my
> calculations are getting done. Sheet 1 column A and column C are the
> only two columns I need to look at. Sheet 2 has the values of what I
> am
> looking for in sheet 1. All data is also alpha characters. No
> numbers.
>
> I need a formual to:
>
> find the value from sheet 2. The value will be repeated several times
> on sheet 1. When finding value, count cell in column A as long as
> long
> as it is not blank.
>
> Thanks for any help you can give.
>
>
> --
> Darrell-


--
Darrell

You can accomplish your counting without any formulae.

Step1 - since you can't change Sheet1, copy it into another sheet(say Sheet3)

Step2 - in Sheet3, delete column B (it is useless)

Step3 - in Sheet3 insert a header row at the very top of the sheet and put
labels ontop of your two columns (Customer and Product)

Step 4- in Sheet 3 select your two columns and
Data -> Pivot Table -> Next -> Next ->Layout

then drag Product into the Row section of the template and drag Customer
into the Data area of the template

OK -> Finish

The resulting Pivot Table should give you exactly what you want.
--
Gary's Student


"Darrell" wrote:

>
> Hope I can clarify. Here is an example of what I'm working with. Sheet
> One is Raw Data dumped in from a seperate report. Sheet 2 is where I'm
> doing all my work. Sheet One can not be changed around because it is
> used by other calculations. Also, Sheet One has 3 columns where column
> B is unneeded information. No Cell will actually contain a number. It
> is all Text except for the desired result.
> SHEET 1
> Customer 1 Product 1
> Product 1
> Product 1
> Customer 2 Product 2
> Product 2
> Product 2
> Customer 3 Product 1
> Product 1
> Product 1
> SHEET 2
> Product 1 =2
> Product 2 =1
> Product 3 =
>
>
> I want the formula, for example, to search for Product 1 (to count the
> number of customers using a specific product) on sheet One. When seeing
> product 1 in Column C, I want it to check Column A for text. If A is
> populated, then count, so the result for this example would be Product
> 1 = 2. The products are listed on Sheet 2 where the result is to be
> posted.
>
> JulieD Wrote:
> > Hi Darrell
> >
> > i'm going to assume you meant, count the number in column A of sheet1
> > as
> > long as the value in column C of sheet 1 is not blank
> > formula on Sheet2 is (where the value you're looking up is in cell A2)
> >
> > =SUMPRODUCT(--(Sheet1!$A$1:$A$100=A2),--(Sheet1!$C$1:$C$100""))
> > check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> > for details on the sumproduct function
> > --
> > Cheers
> > JulieD
> > check out www.hcts.net.au/tipsandtricks.htm
> > ....well i'm working on it anyway
> > "Darrell" [email protected] wrote in message
> > news:[email protected]
> >
> > Hello,
> > I hope I can explaine this well enough. I am using two worksheets.
> > Worksheet 1 is an imported file, worksheet 2 is where all my
> > calculations are getting done. Sheet 1 column A and column C are the
> > only two columns I need to look at. Sheet 2 has the values of what I
> > am
> > looking for in sheet 1. All data is also alpha characters. No
> > numbers.
> >
> > I need a formual to:
> >
> > find the value from sheet 2. The value will be repeated several times
> > on sheet 1. When finding value, count cell in column A as long as
> > long
> > as it is not blank.
> >
> > Thanks for any help you can give.
> >
> >
> > --
> > Darrell-
>
>
> --
> Darrell
>