Count entries ignoring multiple entries

[rfdsc]

Hi,

first time I post here, so let's see if I make myself clear.

I want to count the number of entries in a column associated to another column. At the same time I want to ignore multiple entries. Example:

Company Country MFS
bla Sweden Apples
bla Sweden Oranges
bla Sweden Potatos
bla Italy Apples
bla US Oranges

The formula I am using is:

=SUM(SUMPRODUCT(($B$2:$B$6=B2)*($C$2:$C$6="Sweden"));SUMPRODUCT(($B$2:$B$6=B2)*($C$2:$C$6="Italy"));SUMPRODUCT(($B$2:$B$6=B2)*($C$2:$C$6="US")))

Which retrieves the value 5 for company bla. What I want is the three Sweden entries to count as 1 and the result of the formula should be 3. I have tried with pivot tables and the result is the same.

Thank you in advance for your help.

Cheers,

/R

 

[Aladin Akyurek]

I meant yours worked

I didn't try this one, since I managed to use the one you sent.

Thanks

That's great. Thanks for providing feedback. In case you are (would become) curious about the "~"& and &"" bits, they are used to counteract the adverse effects of some chars like < around the entries in the target range. If you are confident that such does not occur, you can remove these bits from the formula.

 

[rfdsc]

Eheheh, I have been trying to understand that from the start. Thanks for the explanation

/R

 

[T. Valko]

Thank you so much for everyone's help. The latest formula worked. I had defined an array range wrongly, that's why I didn't get any results.

Cheers,

/R

You're welcome. Thanks for the feedback!