Count unique names appearing in one column based on 2 other columns in an Excel worksheet (Network Steve Forum)

Count unique names appearing in one column based on 2 other columns in an Excel worksheet

Dear All,

Is it possible to uniquely count names in a column in Excel based on other 2 columns in the same worksheet ? I am using Excel 2013 and need to uniquely count the names of villages (which may appear any number of times in the column) of districts and sub-districts. I am currently using a Pivot table to uniquely count the names of the villages using 'Distinct Count' in that column, a feature readily available with Excel 2013 Pivot tables. Here comes the problem, some of the names of the villages are exactly similar but belong to completely different districts and sub-districts. Excel 2013 Pivot tables don't see this as a problem, and so counts out any names with similar spellings using its 'Distinct Count' feature, which really doesn't "uniquely count" the villages, and hence gives a false count.

Does anyone know how to workaround this problem ? Greatly appreciate any workable suggestions. Thank you.

February 24th, 2015 1:55am

You can create a new column which is a concatenation of Districts, Sub districts and villages, and apply the distinct count on it from your PivotTable.

To concatenate the different columns you can use this formula (Assuming that column A, B and C contain the Districts, Sub districts and villages).

=CONCATENATE(A2,"/", B2, "/", C2)

You can also write the formula as follows:

= A2 & "/" & B2 & "/" & C2

Marked as answer by maneeshmassey 1 hour 31 minutes ago

Free Windows Admin Tool Kit Click here and download it now

February 24th, 2015 2:17am

Thanks Gil. That was a very smart way of doing something I was intrigued with for so long. I tried it on the Excel sheet and it works wonderfully!

Thank you for your quick reply, really appreciate it a

February 24th, 2015 5:29am