How do I create a CountIF statement for a range of items in a list

[CraigC]

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

 

[Ron Coderre]

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)*(CompAttibData!$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