0

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:

enter image description here

What formula can I use to achieve this?

0

1 Answer 1

0

You may try the SUMPRODUCT formula:

enter image description here

  • 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.

enter image description here

  • 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.

6
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.