CODEWITHSUNDEEP
excelfrequencycountifsumifs
Cambria
Cambria

Reputation: 3

Count unique text values based on criteria in other column

I have two columns titled "Company" and "Department", example below. The third column is what I would like to create, but I am unsure how. I want the third column to show the number of departments for a given company, and only show that number in the first reference to the company.

Click here for example

Upvotes: 0

Views: 999

Answers (3)

user4039065
user4039065

Reputation:

Try this in D2 and fill down.

=IF(COUNTIF(A$2:A2, A2)=1, SUMPRODUCT((A$2:A$999=A2)/(COUNTIFS(B$2:B$999, B$2:B$999&"", A$2:A$999, A2)+(A$2:A$999<>A2))), "")

enter image description here

Upvotes: 0

Clemens
Clemens

Reputation: 9

You could use a pivot table to get distinct count of column department.

While creating the pivot table you need to add the table to the data model so that you expose "distinct count"

(one of numerous sources: http://www.howtoexcelatexcel.com/formulas/excel-tip-how-to-count-unique-items-in-excel-pivot-table/)

Upvotes: -1

pgSystemTester
pgSystemTester

Reputation: 9932

If you create a helper column in Column C that concatenates your two cells, you can then use a function like this:

=if(countif($C$1:C7,C7)=1,sumif(….),"")

If that's not an option, you'll need to use an Array which is likely not something you want to do either.

You might be able to capture your requirements with a pivottable too... Not sure.

Upvotes: 0

Related Questions