How to count distinct values in a column, categorized by values in another column.

Hi,

Just had this problem, and I made up an example to explain it. Please download the attached Excel file.

From the example, Table 1 is to store the raw data. Say we have different car models, A to L. The 2nd column gives the makes of the models. (Here the data type could be numeric.) The 3rd column shows the types of the models.

Table 2 will do the calculation and show the results. Column 2 should show how many different types each make contains. For instance, there should be only 1 type for Jeep, which is the SUV.

Thanks for the help!

Joe

In F3

=SUMPRODUCT(($B$3:$B$14=E3)/COUNTIFS($B$3:$B$14,$B$3:$B$14,$C$3:$C$14,$C$3:$C$14))

Copy down

This should work:

Formula:

Please Login or Register  to view this content.

Thanks again for the answer. Could you please explain how this formula works. I'm trying to apply the formula to the Excel Table cells, unfortunately it didn't work out. Please see the attached file. I used the Excel Table, and used the column name instead of cells to set up the formulas, since Table 1 will keep growing, and the number of rows will be very big.

Originally Posted by Ace_XL

In F3

=SUMPRODUCT(($B$3:$B$14=E3)/COUNTIFS($B$3:$B$14,$B$3:$B$14,$C$3:$C$14,$C$3:$C$14))

Copy down

I found that if you replace the Table references with Sheet name references, the "system" works. I found this out by changing the original range to a table and also the result range to a table and then cut and pasted the second table to a new sheet, the cell references retained the sheet names and cell references.

I too would like a full explanation of this SUMPRODUCT formula. It looks strange but it sure works.

I made the tables work! It involved using named ranges. Take a look. I selected Table1 and then on the Formula Tab clicked on Create from Selection. This created named ranges from all the table columns.

The formula in table2 can be shortened to this:

Formula:

Please Login or Register  to view this content.

You are welcome and thanks for the feedback.