I have a list like this

A B
Var1 Item1
Var2 Item1
Var3 Item2
Var1 Item2
Var3 Item1
Var1 Item1

I would like to count the number of items in Column . Each item can be counted more than once, but only if the name in column a is different. In this case the resultat should be 5 as the last row has same name and Item number as a previous row and is already counted.

Jan

Daddy Sage wrote:

I have a list like this

A B C
Var1 Item1 Var1Item1
Var2 Item1 Var2Item1
Var3 Item2 Var3Item2
Var1 Item2 Var1Item2
Var3 Item1 Var3Item1
Var1 Item1 Var1Item1

I would like to count the number of items in Column . Each item can be
counted more than once, but only if the name in column a is different. In
this case the resultat should be 5 as the last row has same name and Item
number as a previous row and is already counted.

Make a column C = A&B and count in that column.


--
Vie : n.f. maladie mortelle sexuellement transmissible
Benoit chez lui à leraillez.com

onsdag den 2. oktober 2019 kl. 19.01.11 UTC+2 skrev Benoît:
Daddy Sage wrote:

I have a list like this

A B C
Var1 Item1 Var1Item1
Var2 Item1 Var2Item1
Var3 Item2 Var3Item2
Var1 Item2 Var1Item2
Var3 Item1 Var3Item1
Var1 Item1 Var1Item1

I would like to count the number of items in Column . Each item can be
counted more than once, but only if the name in column a is different. In
this case the resultat should be 5 as the last row has same name and Item
number as a previous row and is already counted.

Make a column C = A&B and count in that column.


--
Vie : n.f. maladie mortelle sexuellement transmissible
Benoit chez lui Ã* leraillez.com

I can do that, but it will still count all and duplicates are not to be counted more than once.

onsdag den 2. oktober 2019 kl. 22.36.26 UTC+2 skrev Daddy Sage:
onsdag den 2. oktober 2019 kl. 19.01.11 UTC+2 skrev Benoît:
Daddy Sage wrote:

I have a list like this

A B C
Var1 Item1 Var1Item1
Var2 Item1 Var2Item1
Var3 Item2 Var3Item2
Var1 Item2 Var1Item2
Var3 Item1 Var3Item1
Var1 Item1 Var1Item1

I would like to count the number of items in Column . Each item can be
counted more than once, but only if the name in column a is different.. In
this case the resultat should be 5 as the last row has same name and Item
number as a previous row and is already counted.

Make a column C = A&B and count in that column.


--
Vie : n.f. maladie mortelle sexuellement transmissible
Benoit chez lui Ã* leraillez.com

I can do that, but it will still count all and duplicates are not to be counted more than once.

I found an array formula that will do the trick, but now I wonder if it cn be done without the additional column, that is using oly A and B?

=SUM(IF(C1:C17<"",1/COUNTIF(C1:C17,C1:C17), 0))

Hi,

Am Wed, 2 Oct 2019 14:28:24 -0700 (PDT) schrieb Daddy Sage:

I found an array formula that will do the trick, but now I wonder if it cn be done without the additional column, that is using oly A and B?

=SUM(IF(C1:C17<"",1/COUNTIF(C1:C17,C1:C17), 0))

try:

=SUMPRODUCT((MATCH(A1:A99&"x"&B1:B99,A1:A99&"x"&B1 :B99,0)=ROW(1:99))*1)-1


Regards
Claus B.
--
Windows10
Office 2016

Daddy Sage wrote:

onsdag den 2. oktober 2019 kl. 19.01.11 UTC+2 skrev Benoît:
Daddy Sage wrote:

I have a list like this

A B C
Var1 Item1 Var1Item1
Var2 Item1 Var2Item1
Var3 Item2 Var3Item2
Var1 Item2 Var1Item2
Var3 Item1 Var3Item1
Var1 Item1 Var1Item1

I would like to count the number of items in Column . Each item can be
counted more than once, but only if the name in column a is different.
In this case the resultat should be 5 as the last row has same name
and Item number as a previous row and is already counted.

Make a column C = A&B and count in that column.


I can do that, but it will still count all and duplicates are not to be
counted more than once.

Take a look at :

https://www.ablebits.com/office-addi...lues-in-excel/

--
Vie : n.f. maladie mortelle sexuellement transmissible
Benoit chez lui à leraillez.com

If you can re-arrange the columns, and sort by colA, then colB, the problem becomes much easier:

Var1 Item1 =1
Var1 Item1 =if((A2 = A1,if(B2 = B1,0,1)
Var1 Item2 {copy)
Var2 Item1 {copy)
Var3 Item2 {copy)
Var3 Item1 {copy)

Answer = sum(C1:C6)