0

I have an Excel table with a list of countries, cities and preferred transports, such as the one below:

Table 1

I want to build another table where, for each country, I want to signal the number of transport modes which are repeated, like the one below:

enter image description here

For instance, in Algeria, only "Car" appears more than once, so the value 1 would come up. In Angola the same is true.In Benin, both "Plane" and "Bicycle" appear twice and so the number 2 would come up.

I have tried a mixture of SUM, COUNTIFS AND IFS but have yet to succeed in doing what I want.

How can I do this?

franciscofcosta
  • 833
  • 5
  • 25
  • 53

1 Answers1

1

Let's say that A1:C24 contains your table, including headers, and that E2 contains Algeria, E3 contains Angola, etc, enter the following formula in F2, confirm with CONTROL+SHIFT+ENTER, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$24=E2,IF(LEN($C$2:$C$24)>0,MATCH($C$2:$C$24,$C$2:$C$24,0))),ROW($C$2:$C$24)-ROW($C$2)+1)>1,1))

Hope this helps!

Domenic
  • 7,844
  • 2
  • 9
  • 17