Count instances in a data (Countif? )

[Keibri]

Hi guys,

I would like to ask whether someone can help me with analyzing a set of data. For ease of reference I attached a test data set

I have a long list of data consisting of two columns:
- date
-amount of fruits sold.

I would like to come up with a formula (or maybe pivot?) which would show the number of fruit sold on a particular day.

E.g. If on 5-10-2017 I sold 2 banana and 1 apple. I would like to have a formula which counts the instances of types of fruit sold on that day. That is 2 (bananas and apples)
E.g2: If on 6-10-2017 I sold 3 apples, 2 oranges, 1 banana. I would like to have a formula which counts the instances of types of fruit sold on that day. That is 3 (oranges, bananas and apples)

If not complicated it would also be great to list what fruit was sold next to the no.of instances.

Can someone please help.

As always much appreciated guys!!

Regards

Keibri

[Glenn Kennedy]

H4, copied down:
=SUM(INDEX(($C$4:$C$24=G4)*($B$4:$B$24<>"")/COUNTIFS($C$4:$C$24,$C$4:$C$24&"",$B$4:$B$24,$B$4:$B$24&""),0))

I4, copied across and down:
=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$4:$B$16)/(($C$4:$C$16=$G4)*(COUNTIF($H4:H4, $B$4:$B$16)=0)),1)),"")

[Keibri]

Wow man! just wow! I'm mind blown.

It worked wonder. I m really grateful. To be honest, my data consists of approximately 10,000 lines (so that was amazing) and it worked wonder!!! Thanks a lot!!!!

[Glenn Kennedy]

It might be a bit slow over that no. of lines, but it'll get there.

You're welcome and thanks for the rep.