google sheets
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:
Can anyone help?
Possible but may require some 'compromising':
=arrayformula(right(split(FILTER(B3:B, A3:A>0, LEN(B3:B)>0), ","),1))
in D1 to F10 does find the 2 (which should have been 'underneath' where you put your formula for the purposes of your example) - it also strips out Value, though this could be added back later if required.
in say G1 and copied down to suit:
=arrayformula(LARGE(value(D$1:F$10),row()))
then with all your values in a single column, in say H1 and copied down to suit (upto #NUM!):
=unique(G:G)
The results will be in reverse order from as shown by you (sort?) because of use of LARGE rather than SMALL because if positive values only all the 0 would have come first and there are quite a few of those.
Concatenate results with ="Value "&H1 etc if required.
Apply the following adjustment to your formula.
=COUNTIFS(J6:J365, "=David & Victor", K6:K365, "="&G3)
Best Answer
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.