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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CraigC - 07 Mar 2007 18:52 GMT

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

Reply to this Message

Ron Coderre - 07 Mar 2007 19:21 GMT

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

> 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
[quoted text clipped - 22 lines]
>
> Craig

Reply to this Message