# How to count distinct values in one column for each distinct value in another column in MS Excel?

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.

• If you plan to drag that formula down from E23 to E31 you need to lock most of the row numbers (all the ranges) with \$ absolute row reference. eg. `D23:D31` needs to be `D\$23:D\$31`. Without the \$ absolutes, you will get bad results like E27 and E28.
– user385793
Nov 22, 2019 at 8:08
• @Jeeped,, thanks for valuable observation,, if I use SUMPRODUCT((D\$23:D\$31 <> "") only,, then getting `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
• You're right of course. E23 should be closer to `=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.
– user385793
Nov 22, 2019 at 9:09
• @Jeeped Okay let me try. Nov 22, 2019 at 9:10
• @Jeeped,, thnaks it's working ☺ Nov 22, 2019 at 9:23