1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • Counting with two criterias

    From Daddy Sage@21:1/5 to All on Wed Oct 2 06:19:40 2019
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From =?ISO-8859-1?Q?Beno=EEt?=@21:1/5 to Daddy Sage on Wed Oct 2 19:01:08 2019
    Daddy Sage <[email protected]> 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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Daddy Sage@21:1/5 to All on Wed Oct 2 13:36:22 2019
    onsdag den 2. oktober 2019 kl. 19.01.11 UTC+2 skrev Benoît:
    Daddy Sage <[email protected]> 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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Daddy Sage@21:1/5 to All on Wed Oct 2 14:28:24 2019
    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 <[email protected]> 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))

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Thu Oct 3 01:00:35 2019
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From =?ISO-8859-1?Q?Beno=EEt?=@21:1/5 to Daddy Sage on Thu Oct 3 11:54:26 2019
    Daddy Sage <[email protected]> wrote:

    onsdag den 2. oktober 2019 kl. 19.01.11 UTC+2 skrev Beno�t:
    Daddy Sage <[email protected]> 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-addins-blog/2016/04/07/how-to-count-distinct-and-unique-values-in-excel/

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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)