I need a formula that returns how many times a unique value appears in a given date

Question

In a spreadsheet in Google Spreadsheets, I’m trying to solve this:

17/02/2012  banana
17/02/2012  banana
17/02/2012  banana
17/02/2012  apple
18/02/2012  apple
18/02/2012  apple

I need a formula that returns how many times an unique value appears in a given date.

Example:

• 17/02/2012 - two unique values
• 18/02/2012 - one unique value

Can anyone help?

Answer 1

I don't know how to create a formula, but you can insert a PivotTable to get the same results.

On the spreadsheet enter your data and include a heading for each column (so on your reference above you would add column headings: "date" and "value".

Then choose "Data ... PivotTable report" from the Menu. You are now working on a new tab which is the PivotTable and it references the data on your spreadsheet tab.

In the Report Editor on the right side of the screen make the following selections: "Rows - Add field" and choose "Date"; then choose "Values - Add field" and "Value" ... then "Display: Value, Summarized By "COUNTUNIQUE"

The PivotTable now shows you the unique entries for each date.