Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
Say I have the following data in an Excel sheet:
Country Fruit
USA Banana
USA Apple
USA Apple
USA Orange
USA Banana
USA Apple
UK Banana
UK Banana
UK Apple
I want to count how many distinct fruits are there for each of the countries. Desired output is as follows:
What formula can I use to achieve this?
You may try the SUMPRODUCT formula:
Formula in cell E23, fill it down.
=SUMPRODUCT((D23:D31 <> "")*(C23:C31=C23:C31),1/COUNTIF(D23:D31,D23:D31 & ""))
Edit:
Adding new formula since @Jeeped inspired me to correct the existing formula to get desire results.
Formula in cell E23:
=SUMPRODUCT((C$23:C$31=C23)/(COUNTIFS(C$23:C$31, C23, D$23:D$31, D$23:D$31)+(C$23:C$31<>C23)))
N.B.
Since you are suppose to drag formula down therefore to avoid appearing ZEROs after row 31, you may use this revised version of the formula.
=IF(SUMPRODUCT((C$23:C$31=C23)/(COUNTIFS(C$23:C$31, C23, D$23:D$31, D$23:D$31)+(C$23:C$31<>C23)))=0,"",SUMPRODUCT((C$23:C$31=C23)/(COUNTIFS(C$23:C$31, C23, D$23:D$31, D$23:D$31)+(C$23:C$31<>C23))))
Adjust cell references in the formula as needed.
D23:D31 needs to be D$23:D$31. Without the $ absolutes, you will get bad results like E27 and E28.
3,4,4,4,3,3,3,3 & 2,,, if Absolute reference with SUMPRODUCT & COUNTIF both then getting only 3s .
Nov 22, 2019 at 8:34
=SUMPRODUCT((C$23:C$31=C23)/(COUNTIFS(C$23:C$31, C23, D$23:D$31, D$23:D$31)+(C$23:C$31<>C23))) then dragged down.