+ Reply to Thread
Results 1 to 21 of 21

Count the number of unique Numbers in a column

  1. #1
    Registered User
    Join Date
    05-04-2006
    Location
    Sweden
    Posts
    22

    Question Count the number of unique Numbers in a column

    Hi!!

    i'm currently trying to count the number of unique numbers in a Column using this formula: =SUMPRODUCT((A3:A3002<>"")/COUNTIF(A3:A4002;A3:A3002&""))
    So far so good.....But I want to add some conditions like only count the numbers that meet the condition say N1:N3000="SE*"

    I tried a couple of things but nothing seem to work can you please help out.....Don't laugh and I will paste my own failed solutions:
    =SUMPRODUCT((A1:A3000<>"");(AND(OR(N1:N3000="SE*";N1:N3000="INC*")/COUNTIF(A1:A3000;A1:A3000&""))))
    On this one I get #VALUE! and i can't figure out why.
    I have also tried to put the AND/OR condition before the actual starting of the array of the SUMPRODUCT but nothing.

  2. #2
    Toppers
    Guest

    RE: Count the number of unique Numbers in a column

    Try .....

    =SUMPRODUCT(--(LEFT(N3:N3000,3)="INC"),(A3:A3000<>"")/COUNTIF(A3:A3000,A3:A3000&"")) +
    SUMPRODUCT(--(LEFT(N3:N3000,2)="SE"),(A3:A3000<>"")/COUNTIF(A3:A3000,A3:A3000&""))

    I don't think you can OR with SUMPRODUCT (but I could be wrong!)

    HTH

    "ajajmannen" wrote:

    >
    > Hi!!
    >
    > i'm currently trying to count the number of unique numbers in a Column
    > using this formula:
    > =SUMPRODUCT((A3:A3002<>"")/COUNTIF(A3:A4002;A3:A3002&""))
    > So far so good.....But I want to add some conditions like only count
    > the numbers that meet the condition say N1:N3000="SE*"
    >
    > I tried a couple of things but nothing seem to work can you please help
    > out.....Don't laugh and I will paste my own failed solutions:
    > =SUMPRODUCT((A1:A3000<>"");(AND(OR(N1:N3000="SE*";N1:N3000="INC*")/COUNTIF(A1:A3000;A1:A3000&""))))
    > On this one I get #VALUE! and i can't figure out why.
    > I have also tried to put the AND/OR condition before the actual
    > starting of the array of the SUMPRODUCT but nothing.
    >
    >
    > --
    > ajajmannen
    > ------------------------------------------------------------------------
    > ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
    > View this thread: http://www.excelforum.com/showthread...hreadid=538999
    >
    >


  3. #3
    Domenic
    Guest

    Re: Count the number of unique Numbers in a column

    Try...

    =COUNT(1/FREQUENCY(IF(LEFT(N1:N3000,2)="SE",IF(A1:A3000<>"",A1:A3000)),IF
    (LEFT(N1:N3000,2)="SE",IF(A1:A3000<>"",A1:A3000))))

    ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

    Hope this helps!

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > Hi!!
    >
    > i'm currently trying to count the number of unique numbers in a Column
    > using this formula:
    > =SUMPRODUCT((A3:A3002<>"")/COUNTIF(A3:A4002;A3:A3002&""))
    > So far so good.....But I want to add some conditions like only count
    > the numbers that meet the condition say N1:N3000="SE*"
    >
    > I tried a couple of things but nothing seem to work can you please help
    > out.....Don't laugh and I will paste my own failed solutions:
    > =SUMPRODUCT((A1:A3000<>"");(AND(OR(N1:N3000="SE*";N1:N3000="INC*")/COUNTIF(A1:
    > A3000;A1:A3000&""))))
    > On this one I get #VALUE! and i can't figure out why.
    > I have also tried to put the AND/OR condition before the actual
    > starting of the array of the SUMPRODUCT but nothing.


  4. #4
    Domenic
    Guest

    Re: Count the number of unique Numbers in a column

    I just noticed the second part of the question. To count the number of
    unique numbers in Column A where the corresponding value in Column N is
    either SE or INC, try...

    =COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH(N1:N3000,{"SE","INC"},0)),IF(A1:A300
    0<>"",A1:A3000)),IF(ISNUMBER(MATCH(N1:N3000,{"SE","INC"},0)),IF(A1:A3000<
    >"",A1:A3000))))


    ....confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > Hi!!
    >
    > i'm currently trying to count the number of unique numbers in a Column
    > using this formula:
    > =SUMPRODUCT((A3:A3002<>"")/COUNTIF(A3:A4002;A3:A3002&""))
    > So far so good.....But I want to add some conditions like only count
    > the numbers that meet the condition say N1:N3000="SE*"
    >
    > I tried a couple of things but nothing seem to work can you please help
    > out.....Don't laugh and I will paste my own failed solutions:
    > =SUMPRODUCT((A1:A3000<>"");(AND(OR(N1:N3000="SE*";N1:N3000="INC*")/COUNTIF(A1:
    > A3000;A1:A3000&""))))
    > On this one I get #VALUE! and i can't figure out why.
    > I have also tried to put the AND/OR condition before the actual
    > starting of the array of the SUMPRODUCT but nothing.


  5. #5
    Registered User
    Join Date
    05-04-2006
    Location
    Sweden
    Posts
    22

    Unique numbers count???

    Hi!!

    I tried your formula but excel is complaining about the Lookup value in the first Match formula?

    When I look at the help it states that the Value I'm looking for should be second and the Array second but I don't seem to get it right anyway......

    Please help me here
    First of all I probably have a diffrent version of excel I use smicolon instead of colon.
    I tried something like this but now it stops at last parentes after the first IF statement
    =COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH({"SE";"INC"};(N1:N2385);0;IF(A1:A3000<>"";A1:A3000))) IT stops here and I don't know why?? ;IF(ISNUMBER(MATCH({"SE","INC"};(N1:N2385,);0;IF(A1:A3000<>"";A1:A3000)))

    =COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH({"SE";"INC"};(N1:N2385);0;IF(A1:A3000<>"";A1:A3000)));IF(ISNUMBER(MATCH({"SE","INC"};(N1:N2385,);0;IF(A1:A3000<>"";A1:A3000)))

  6. #6
    Domenic
    Guest

    Re: Count the number of unique Numbers in a column

    Try...

    =COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH(N1:N3000,{"SE";"INC"};0));IF(A1:A300
    0<>"";A1:A3000));IF(ISNUMBER(MATCH(N1:N3000;{"SE";"INC"};0));IF(A1:A3000<
    >"";A1:A3000))))


    ....confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > Hi!!
    >
    > I tried your formula but excel is complaining about the Lookup value in
    > the first Match formula?
    >
    > When I look at the help it states that the Value I'm looking for should
    > be second and the Array second but I don't seem to get it right
    > anyway......
    >
    > Please help me here
    > First of all I probably have a diffrent version of excel I use smicolon
    > instead of colon.
    > I tried something like this but now it stops at last parentes after the
    > first IF statement
    >
    >
    > =COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH({"SE";"INC"};(N1:N2385);0;IF(A1:A3000<>""
    > ;A1:A3000)))
    > IT stops here and I don't know why??
    > ;IF(ISNUMBER(MATCH({"SE","INC"};(N1:N2385,);0;IF(A1:A3000<>"";A1:A3000)))
    >
    > =COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH({"SE";"INC"};(N1:N2385);0;IF(A1:A3000<>""
    > ;A1:A3000)));IF(ISNUMBER(MATCH({"SE","INC"};(N1:N2385,);0;IF(A1:A3000<>"";A1:A
    > 3000)))


  7. #7
    Registered User
    Join Date
    05-04-2006
    Location
    Sweden
    Posts
    22
    Quote Originally Posted by Domenic
    Try...

    =COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH(N1:N3000,{"SE";"INC"};0));IF(A1:A300
    0<>"";A1:A3000));IF(ISNUMBER(MATCH(N1:N3000;{"SE";"INC"};0));IF(A1:A3000<
    >"";A1:A3000))))


    ....confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > Hi!!
    >
    > I tried your formula but excel is complaining about the Lookup value in
    > the first Match formula?
    >
    > When I look at the help it states that the Value I'm looking for should
    > be second and the Array second but I don't seem to get it right
    > anyway......
    >
    > Please help me here
    > First of all I probably have a diffrent version of excel I use smicolon
    > instead of colon.
    > I tried something like this but now it stops at last parentes after the
    > first IF statement
    >
    >
    > =COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH({"SE";"INC"};(N1:N2385);0;IF(A1:A3000<>""
    > ;A1:A3000)))
    > IT stops here and I don't know why??
    > ;IF(ISNUMBER(MATCH({"SE","INC"};(N1:N2385,);0;IF(A1:A3000<>"";A1:A3000)))
    >
    > =COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH({"SE";"INC"};(N1:N2385);0;IF(A1:A3000<>""
    > ;A1:A3000)));IF(ISNUMBER(MATCH({"SE","INC"};(N1:N2385,);0;IF(A1:A3000<>"";A1:A
    > 3000)))
    Ok one step closer.....It counted 0......I think i know why.....I would like to use a wildcard on the SE and INC....As all of the values in that column either starts with SE or INC so a value can look like INC96835 or INC99784....Is there a way to get it to match the value with either a wildcard or by stating "starts with"?????

  8. #8
    Domenic
    Guest

    Re: Count the number of unique Numbers in a column

    Try the following formula instead...

    =COUNT(1/FREQUENCY(IF((LEFT(N1:N3000;2)="SE")+(LEFT(N1:N3000;3)="INC");IF
    (A1:A3000<>"";A1:A3000));IF((LEFT(N1:N3000;2)="SE")+(LEFT(N1:N3000;3)="IN
    C");IF(A1:A3000<>"";A1:A3000))))

    ....confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > Ok one step closer.....It counted 0......I think i know why.....I would
    > like to use a wildcard on the SE and INC....As all of the values in that
    > column either starts with SE or INC so a value can look like INC96835 or
    > INC99784....Is there a way to get it to match the value with either a
    > wildcard or by stating "starts with"?????


  9. #9
    Registered User
    Join Date
    05-04-2006
    Location
    Sweden
    Posts
    22
    Quote Originally Posted by Domenic
    Try the following formula instead...

    =COUNT(1/FREQUENCY(IF((LEFT(N1:N3000;2)="SE")+(LEFT(N1:N3000;3)="INC");IF
    (A1:A3000<>"";A1:A3000));IF((LEFT(N1:N3000;2)="SE")+(LEFT(N1:N3000;3)="IN
    C");IF(A1:A3000<>"";A1:A3000))))

    ....confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > Ok one step closer.....It counted 0......I think i know why.....I would
    > like to use a wildcard on the SE and INC....As all of the values in that
    > column either starts with SE or INC so a value can look like INC96835 or
    > INC99784....Is there a way to get it to match the value with either a
    > wildcard or by stating "starts with"?????

    I must say many thanks for all the help.....But It still counts it to 0 and I know that it should be around 700 entries at least.

    Please help out.....I don't know what more information to give to be able to get the correct formula.

  10. #10
    Domenic
    Guest

    Re: Count the number of unique Numbers in a column

    One possibility is that the numbers in Column A are being recognized as
    text. What do you get when you try...

    =ISNUMBER(A2)

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > I must say many thanks for all the help.....But It still counts it to 0
    > and I know that it should be around 700 entries at least.
    >
    > Please help out.....I don't know what more information to give to be
    > able to get the correct formula.


  11. #11
    Registered User
    Join Date
    05-04-2006
    Location
    Sweden
    Posts
    22
    Quote Originally Posted by Domenic
    One possibility is that the numbers in Column A are being recognized as
    text. What do you get when you try...

    =ISNUMBER(A2)

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > I must say many thanks for all the help.....But It still counts it to 0
    > and I know that it should be around 700 entries at least.
    >
    > Please help out.....I don't know what more information to give to be
    > able to get the correct formula.
    You were correct there It gave a false value but I changed that and got a TRUE value......but it is still giving the result 0.
    Ok to give more information: The vaules in Column A is a 7 digit number like 1937451. The Value in the N Column can look like this: SE96745 or like INC-095667.
    The numbers in Column A can appear several times and i only want to count how many unique hits i get.
    The values in column N can also be NOXXXXX and DKXXXXX but I only want to count the unique numbers in Column A that meet the condition SE* or INC*.
    I would like to get.

    This formula is really getting on my nerv......And again I want to thank you for helping out and I hope that we will find the answer

  12. #12
    Domenic
    Guest

    Re: Count the number of unique Numbers in a column

    Then it's likely that the numbers for the remainder of the column are
    also being recognized as text. Try the following...

    1) Select an empty cell

    2) Edit > Copy

    3) Select Column A

    4) Edit > Paste Special > Add > Ok

    Does this help?

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > You were correct there It gave a false value but I changed that and got
    > a TRUE value......but it is still giving the result 0.
    > Ok to give more information: The vaules in Column A is a 7 digit number
    > like 1937451. The Value in the N Column can look like this: SE96745 or
    > like INC-095667.
    > The numbers in Column A can appear several times and i only want to
    > count how many unique hits i get.
    > The values in column N can also be NOXXXXX and DKXXXXX but I only want
    > to count the unique numbers in Column A that meet the condition SE* or
    > INC*.
    > I would like to get.
    >
    > This formula is really getting on my nerv......And again I want to
    > thank you for helping out and I hope that we will find the answer


  13. #13
    Registered User
    Join Date
    05-04-2006
    Location
    Sweden
    Posts
    22
    Quote Originally Posted by Domenic
    Then it's likely that the numbers for the remainder of the column are
    also being recognized as text. Try the following...

    1) Select an empty cell

    2) Edit > Copy

    3) Select Column A

    4) Edit > Paste Special > Add > Ok

    Does this help?

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > You were correct there It gave a false value but I changed that and got
    > a TRUE value......but it is still giving the result 0.
    > Ok to give more information: The vaules in Column A is a 7 digit number
    > like 1937451. The Value in the N Column can look like this: SE96745 or
    > like INC-095667.
    > The numbers in Column A can appear several times and i only want to
    > count how many unique hits i get.
    > The values in column N can also be NOXXXXX and DKXXXXX but I only want
    > to count the unique numbers in Column A that meet the condition SE* or
    > INC*.
    > I would like to get.
    >
    > This formula is really getting on my nerv......And again I want to
    > thank you for helping out and I hope that we will find the answer
    Tried that to...but it seem like the problem is elsewhere...
    I will copy a sample of the to columns in here and maybe you can try the formula in action.
    My Version of excel i 2002 with sp3.
    Column N
    USER_ID
    SE096932
    SE096932
    SE096932
    SE096932
    SE096932
    INC-97173
    INC-97173
    SE096932
    SE096932

    Column A
    PROBLEM_ID
    1499986
    1499986
    1499986
    1499986
    1758434
    1929352
    1929352
    1936837
    1936837
    If this formula would work then the result for the sample would be 4
    Hopefully this will make it easier for you
    And just for the record....I really appreciate the help.

  14. #14
    Domenic
    Guest

    Re: Count the number of unique Numbers in a column

    Based on the data supplied, the formula does indeed return 4. If you'd
    like, I can email you a sample file. If so, send me your email
    address...

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > Tried that to...but it seem like the problem is elsewhere...
    > I will copy a sample of the to columns in here and maybe you can try
    > the formula in action.
    > My Version of excel i 2002 with sp3.
    > Column N
    > USER_ID
    > SE096932
    > SE096932
    > SE096932
    > SE096932
    > SE096932
    > INC-97173
    > INC-97173
    > SE096932
    > SE096932
    >
    > Column A
    > PROBLEM_ID
    > 1499986
    > 1499986
    > 1499986
    > 1499986
    > 1758434
    > 1929352
    > 1929352
    > 1936837
    > 1936837
    > If this formula would work then the result for the sample would be 4
    > Hopefully this will make it easier for you
    > And just for the record....I really appreciate the help.


  15. #15
    Registered User
    Join Date
    05-04-2006
    Location
    Sweden
    Posts
    22
    Quote Originally Posted by Domenic
    Based on the data supplied, the formula does indeed return 4. If you'd
    like, I can email you a sample file. If so, send me your email
    address...

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > Tried that to...but it seem like the problem is elsewhere...
    > I will copy a sample of the to columns in here and maybe you can try
    > the formula in action.
    > My Version of excel i 2002 with sp3.
    > Column N
    > USER_ID
    > SE096932
    > SE096932
    > SE096932
    > SE096932
    > SE096932
    > INC-97173
    > INC-97173
    > SE096932
    > SE096932
    >

    Column A
    > PROBLEM_ID
    > 1499986
    > 1499986
    > 1499986
    > 1499986
    > 1758434
    > 1929352
    > 1929352
    > 1936837
    > 1936837
    > If this formula would work then the result for the sample would be 4
    > Hopefully this will make it easier for you
    > And just for the record....I really appreciate the help.
    My e-mail is: [email protected]

  16. #16
    Domenic
    Guest

    Re: Count the number of unique Numbers in a column

    Sample file sent...

    In article <[email protected]>,
    ajajmannen <[email protected]>

    > My e-mail is: ......


  17. #17
    Registered User
    Join Date
    05-04-2006
    Location
    Sweden
    Posts
    22

    Now it is working

    Thanks alot it was really a great help....

    What I don't understand is why the formula didn't work before you posted it in a Sample file.....I really did CTRL, SHIFT + ENTER.

    But it doesn't matter it do work now

    This will be a big help for me as this reports needs to be analyzed every month.

  18. #18
    Registered User
    Join Date
    05-04-2006
    Location
    Sweden
    Posts
    22

    Smile

    Quote Originally Posted by ajajmannen
    Thanks alot it was really a great help....

    What I don't understand is why the formula didn't work before you posted it in a Sample file.....I really did CTRL, SHIFT + ENTER.

    But it doesn't matter it do work now

    This will be a big help for me as this reports needs to be analyzed every month.
    Would you find me to annoying if I asked a followup question regarding the formula.
    I'm now trying to add a AND condition to the IF statement. But it doesn't work.
    the formula looks like this:
    =COUNT(1/FREQUENCY(IF((LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N2385;3)="INC");AND(Sheet1!T2:T2385="ABD");IF(Sheet1!A2:A2385<>"";Sheet1!A2:A2385));IF((LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N2385;3)="INC");AND(Sheet1!T2:T2385="ABD");IF(Sheet1!A2:A2385<>"";Sheet1!A2:A2385))))
    I have tried to use CTRL+****+ENTER but it only counts the same number even thou I change the second AND logical condition to another value.
    If it isn't to much to ask please help me find out what the problem is?

    This FORUM is really the best ever for that matter

  19. #19
    Domenic
    Guest

    Re: Count the number of unique Numbers in a column

    No problem at all. Here you go...

    =COUNT(1/FREQUENCY(IF((LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N238
    5;3)="INC");IF(Sheet1!T2:T2385="ABD";IF(Sheet1!A2:A2385<>"";Sheet1!A2:A23
    85)));IF((LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N2385;3)="INC");I
    F(Sheet1!T2:T2385="ABD";IF(Sheet1!A2:A2385<>"";Sheet1!A2:A2385)))))

    ....confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > Would you find me to annoying if I asked a followup question regarding
    > the formula.
    > I'm now trying to add a AND condition to the IF statement. But it
    > doesn't work.
    > the formula looks like this:
    > =COUNT(1/FREQUENCY(IF((LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N2385;3)=
    > "INC");AND(Sheet1!T2:T2385="ABD");IF(Sheet1!A2:A2385<>"";Sheet1!A2:A2385));IF(
    > (LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N2385;3)="INC");AND(Sheet1!T2:T
    > 2385="ABD");IF(Sheet1!A2:A2385<>"";Sheet1!A2:A2385))))
    > I have tried to use CTRL+****+ENTER but it only counts the same number
    > even thou I change the second AND logical condition to another value.
    > If it isn't to much to ask please help me find out what the problem
    > is?
    >
    > This FORUM is really the best ever for that matter


  20. #20
    Registered User
    Join Date
    05-04-2006
    Location
    Sweden
    Posts
    22
    Thanks again .....One question thou.....why doesn't it work with the AND condition after the IF statement...I have several other forumlas where I have that?

    Is it because the COUNT or frequency fromula can't combine AND?

  21. #21
    Domenic
    Guest

    Re: Count the number of unique Numbers in a column

    It's because the AND function only returns a single value, not an
    array....

    Hope this helps!

    In article <[email protected]>,
    ajajmannen <[email protected]>
    wrote:

    > Thanks again .....One question thou.....why doesn't it work with the AND
    > condition after the IF statement...I have several other forumlas where I
    > have that?
    >
    > Is it because the COUNT or frequency fromula can't combine AND?


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1