counting unique values in one column for each value in another

[seancain99]

I have a spreadsheet with many rows. I am quite familiar with pivot tables and perhaps I am just missing something, but I am unable to do what I want within a pivot.

Let's say in column A and B I have the following values

yellow chocolate
yellow chocolate
blue chocolate
yellow vanilla
yellow vanilla
blue vanilla
black strawberrry
red vanilla

I would like to report on how many unique flavors of ice cream there are by color. The resulting table would show:

yellow 2
blue 2
black 1
red 1

So rather than defineing a range and creating a pivot that would show the total count of flavors by color, I simply want a count of the unique flavors by color.

Thanks

[jplank]

Try and see if this works: http://stackoverflow.com/questions/1...-unique-values

[Kevin UK]

Hi seancain99

Perhaps something like the attached file.

[oeldere]

With helpcolumn and pivot table.

See attached file.

[seancain99]

Try and see if this works: http://stackoverflow.com/questions/1...-unique-values

Thanks very much. I hadn't thought of that, but it is a creative solution. It would be nice to have that option to count unique built into pivots.

[Kevin UK]

It does, Excel 2013!

Thanks very much. I hadn't thought of that, but it is a creative solution. It would be nice to have that option to count unique built into pivots.