Hi,

SUMPRODUCT computes arrays in a similar way to array formulas; it's still going to be slow. Based on the data being in A2:B9 (same as before), put this formula in C2 and copy down to C9:
=COUNTIFS(A2:$A$2,A2,B2:$B$2,B2)
Then this formula will give you the count of distinct names corresponding to Apple:
=COUNTIFS(B2:B9,"Apple",C2:C9,1)
This could be greatly optimised if sorting was allowed.