Counting with two criterias
 

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

Counting with two criterias
 

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

Counting with two criterias
 

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.

Counting with two criterias
 

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))

Counting with two criterias
 

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

Counting with two criterias
 

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)