Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a CountIF statement for a range of items in a list
I want to be able to create a table with one column providing a list of
companies with the counts of their values in a series of other columns. In the column where the original company data comes from, the company names appear more than once in the column (but now I am wanting one value per company). My columns a Company, Attribute 1, Attribute 2, Attribute 3, Attribute infinity. In the company column, company names can appear more than once. In the Attribute columns are the values, NA, positive, neutral, mixed, and negative. I am wanting to produce a new worksheet with 3 columns. Column 1: Company Name. In each row, the company name appears only once. Column 2: Positive- The count of the number of times that for that company, the value 'positive' appears for the range of attributes: Attribute 1-Attribute Infinity Column 3: Negative-The count of the number of times that for that company, the value 'neutral' appears for the range of attributes: Attribute 1-Attribute Infinity I'm sure this could be done easier using Pivot Tables, but the formula for doing this would be helpful on a number of things. thank you! Craig |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a CountIF statement for a range of items in a list
Try something like this:
On a sheet named: CompAttribData A1:F7 contains this data list CompanyName Attrib_1 Attrib_2 Attrib_3 Attrib_4 Attrib_5 Company_01 NA Positive Neutral Mixed Negative Company_02 Negative NA Positive Neutral Mixed Company_03 Mixed Negative NA Positive Neutral Company_01 Neutral Mixed Negative NA Positive Company_02 Positive Neutral Mixed Negative NA Company_03 NA Positive Neutral Mixed Negative (Hopefully text wrap won't make that list unreadable) Then....on a sheet named: CompSummary Create this table, beginning in cell A1 (blank) NA Positive Neutral Mixed Negative Company_01 (blank) (blank) (blank) (blank) (blank) Company_02 (blank) (blank) (blank) (blank) (blank) Company_03 (blank) (blank) (blank) (blank) (blank) NEXT.....(still on that sheet) B2: =SUMPRODUCT((CompAttibData!$A$1:$A$20=$A2)*(CompAt tibData!$B$1:$F$20=B$1)) Copy that formula to the right and down as far as you need Using my posted data, this would be the result table: (blank) NA Positive Neutral Mixed Negative Company_01 2 2 2 2 2 Company_02 2 2 2 2 2 Company_03 2 2 2 2 2 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "CraigC" wrote: I want to be able to create a table with one column providing a list of companies with the counts of their values in a series of other columns. In the column where the original company data comes from, the company names appear more than once in the column (but now I am wanting one value per company). My columns a Company, Attribute 1, Attribute 2, Attribute 3, Attribute infinity. In the company column, company names can appear more than once. In the Attribute columns are the values, NA, positive, neutral, mixed, and negative. I am wanting to produce a new worksheet with 3 columns. Column 1: Company Name. In each row, the company name appears only once. Column 2: Positive- The count of the number of times that for that company, the value 'positive' appears for the range of attributes: Attribute 1-Attribute Infinity Column 3: Negative-The count of the number of times that for that company, the value 'neutral' appears for the range of attributes: Attribute 1-Attribute Infinity I'm sure this could be done easier using Pivot Tables, but the formula for doing this would be helpful on a number of things. thank you! Craig |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif count items not in data range | Excel Discussion (Misc queries) | |||
How do I create a list of items in a column? | Excel Worksheet Functions | |||
how do i create a drop down list of items from a different file | Excel Worksheet Functions | |||
List of items appeared in range | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |