Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formula needed
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. |
#2
|
|||
|
|||
Hi!
Need a better explanation! When finding value, count cell in column A as long as long as it is not blank. That doesn't make sense! Biff "Darrell" wrote in message ... 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 |
#3
|
|||
|
|||
as Biff said your explanation is a bit vaugue.Have you looked at the count
functions (count,counta, countif) -- hope this helps Paul "Darrell" wrote: 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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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. Quote:
|
#6
|
|||
|
|||
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" wrote in message ...- 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 |
#7
|
|||
|
|||
Thanks for the help. I like that way a lot better. I actually got a formula to work, and it probably follows along with the suggested earlier. It is written with the names of the tabs I had to use, but maybe someone else will be able to use it. Again, thanks to everyone.
=SUMPRODUCT(--('Call Frequency'!$A$1:$A$50000<"")/COUNTIF('Call Frequency'!$A$1:$A$50000,'Call Frequency'!$A$1:$A$50000&""),--('Call Frequency'!$C$1:$C$50000='Repeat Model Type Breakdown'!A2)) Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Formula needed | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |