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

 

[HalfAce]

Hi rfdsc, welcome to the board.

Does something like this get you closer?
Rather than summing the returns that meet your criteria, it just counts them.

=COUNTA(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")))

Hope it helps.

 

[rfdsc]

Hi Halface,

thanks for your reply.

The formula you sent will count the number of entries from the sumproduct, including the 0s, so I will have the same value always when I run the formula in the whole table.

Is there a way to ignore the 0s?

Cheers
/R

 

[Aladin Akyurek]

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

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF($C$2:$C$6<>"",
   IF($B$2:$B$6=J2,MATCH("~"&$C$2:$C$6,$C$2:$C$6&"",0))),
    ROW($C$2:$C$6)-ROW($C$2)+1),1))

where J2 houses a company of interest.

 

[rfdsc]

Hi Aladin,

thank you so much for your reply.

I tried your formula, but after correcting it to my version of excel (forgot to mention I am using office 2010) I get an #N/A as a result. See below the corrected formula.

=SUM(IF(FREQUEN<wbr>CY(IF($C$2:$C$6<wbr><>"";IF($B$2:$B<wbr>$6=J2;MATCH("~"<wbr>&$C$2:$C$6;$C$2<wbr>:$C$6&"";0)));I<wbr>F($B$2:$B$6=J2;<wbr>MATCH("~"&$C$2:<wbr>$C$6;$C$2:$C$6&<wbr>"";0)));ROW($C$<wbr>2:$C$6)-ROW($C$<wbr>2)+1)*0;1)

Cheers,

/R

 

[Aladin Akyurek]

Hi Aladin,

thank you so much for your reply.

I tried your formula, but after correcting it to my version of excel (forgot to mention I am using office 2010) I get an #N/A as a result. See below the corrected formula.

=SUM(IF(FREQUEN<WBR>CY(IF($C$2:$C$6<WBR><>"";
   IF($B$2:$B<WBR>$6=J2;MATCH("~"<WBR>&$C$2:$C$6;$C$2<WBR>:$C$6&"";0)));
    I<WBR>F($B$2:$B$6=J2;<WBR>MATCH("~"&$C$2:<WBR>$C$6;$C$2:$C$6&<WBR>"";0)));
     ROW($C$<WBR>2:$C$6)-ROW($C$<WBR>2)+1)*0;1)

Cheers,

/R

It's control+shift+enter...

=SUM(IF(FREQUEN<WBR>CY(IF($C$2:$C$6<WBR><>"";
   IF($B$2:$B<WBR>$6=J2;MATCH("~"<WBR>&$C$2:$C$6;$C$2<WBR>:$C$6&"";0)));
    ROW($C$<WBR>2:$C$6)-ROW($C$<WBR>2)+1);1))

where J2 = bla.

 

[T. Valko]

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

What exactly do you want to count?

The unique countries that correspond to company "bla" ?

If so, try this array formula**:

=SUM(IF(FREQUENCY(IF(A2:A6="bla",MATCH(B2:B6,B2:B6,0)),ROW(B2:B6)-ROW(B2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes no empty cells within the Country data range.

 

[rfdsc]

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

 

[Aladin Akyurek]

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

If

=SUM(IF(FREQUENCY(IF(A2:A6="bla",MATCH(B2:B6,B2:B6,0)),ROW(B2:B6)-ROW(B2)+1),1))

works, adjusted for the relevant ranges, so would,

=SUM(IF(FREQUEN<WBR>CY(IF($B$2:$B<WBR>$6<WBR><>"";
   IF($A$2:$A$6=J2;MATCH("~"<WBR>&$$B$2:$B<WBR>$6<WBR>;$B$2:$B<WBR>$6<WBR>&"";0)));
    ROW($B$2:$B<WBR>$6<WBR>)-ROW($B$<WBR>2)+1);1))

where J2 = bla.

 

[rfdsc]

I meant yours worked

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

Thanks