Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 46

Thread: Formulas to count including Unique Numbers

  1. #11
    Board Regular
    Join Date
    Feb 2018
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formulas to count including Unique Numbers

    Hi M,

    Thanks again for correcting me. Yes yo are correct. It would be 6 because as you say Brian should have been counted.

  2. #12
    Board Regular
    Join Date
    Feb 2018
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formulas to count including Unique Numbers

    Hi M,

    This formula works when I do it small scale on the test data I use. When I try to expand it out onto the real data I just get an N/A in the cell. The formula I'm trying to use is as follows :-

    =SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A500,IF((I2:I500<>"")+(K2:K500="Yes"),A2:A500),0)),MATCH(A2:A500,A2:A500,0)),ROW(A2:A500)-ROW(A2)+1),1)) - followed by Ctrl/Shift & enter.

    Any ideas on my mistake ?

    Thanks

  3. #13
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,267
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formulas to count including Unique Numbers

    Quote Originally Posted by Graham C1600 View Post
    Hi M,

    Thanks again for correcting me. Yes yo are correct. It would be 6 because as you say Brian should have been counted.

    When I tested the formula I saw that I was wrong, too
    Actually the answer should be 7 - Andrew should be counted as well.

    Try
    =SUM(IF(D2:D14="No",--ISNUMBER(MATCH(A2:A14,IF((C2:C14<>"")*(D2:D14="No"),A2:A14),0))))
    Ctrl+Shift+Enter

    M.
    Last edited by Marcelo Branco; Jun 14th, 2019 at 10:02 AM.

  4. #14
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,267
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formulas to count including Unique Numbers

    Quote Originally Posted by Graham C1600 View Post
    Hi M,

    This formula works when I do it small scale on the test data I use. When I try to expand it out onto the real data I just get an N/A in the cell. The formula I'm trying to use is as follows :-

    =SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A500,IF((I2:I500<>"")+(K2:K500="Yes"),A2:A500),0)),MATCH(A2:A500,A2:A500,0)),ROW(A2:A500)-ROW(A2)+1),1)) - followed by Ctrl/Shift & enter.

    Any ideas on my mistake ?

    Thanks
    What query are you referring to? Are there blank cells in A2:A500? Or cells with errors?

    M.

  5. #15
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,267
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formulas to count including Unique Numbers

    See if this works

    =SUM(IF(FREQUENCY(IF(A2:A500<>"",IF(ISNA(MATCH(A2:A500,IF((I2:I500<>"")+(K2:K500="Yes"),A2:A500),0)),MATCH(A2:A500,A2:A500,0))),ROW(A2:A500)-ROW(A2)+1),1))
    Ctrl+Shift+Enter

    M.

  6. #16
    Board Regular
    Join Date
    Feb 2018
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formulas to count including Unique Numbers

    Thanks M.

    4
    th
    query. I need to count the total amount of User ID’s where at least 1 person has data in their Last Logon box and the UTC =”No”.

    This one now. The answer should be 5.

    Everything else is working so thanks.

  7. #17
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,267
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formulas to count including Unique Numbers

    Shouldn't the result be 7?
    ID1 = 3; ID5 = 1; ID7 = 1; ID8 = 2 (3+1+1+2=7)

    M.

  8. #18
    Board Regular
    Join Date
    Feb 2018
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formulas to count including Unique Numbers

    Quote Originally Posted by Graham C1600 View Post
    Thanks M.

    4
    th
    query. I need to count the total amount of User ID’s where at least 1 person has data in their Last Logon box and the UTC =”No”.

    This one now. The answer should be 5.

    Everything else is working so thanks.
    Hi M,

    Bad wording from me.

    I need to count the total amount of User ID’s where there is data in their Last Logon box and the UTC =”No”.

    Gary
    Stephen
    Simon
    Darren
    John
    Thanks

  9. #19
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,267
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formulas to count including Unique Numbers

    Quote Originally Posted by Graham C1600 View Post

    I need to count the total amount of User ID’s where there is data in their Last Logon box and the UTC =”No”.

    Gary
    Stephen
    Simon
    Darren
    John
    Thanks
    Try
    =COUNTIFS(C2:C14,"<>",D2:D14,"No")

    M.

  10. #20
    Board Regular
    Join Date
    Feb 2018
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formulas to count including Unique Numbers

    Thanks M works fine.

    Next puzzle.

    I need to count the total amount of User ID’s where there is no data in the Last Logon Field and the UTC=”No”. This count needs to be similar to the 2nd query (=SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A14,IF((C2:C14<>"")+(D2:D14="Yes"),A2:A14),0)),MATCH(A2:A14,A2:A14,0)),ROW(A2:A14)-ROW(A2)+1),1))) in that if there is a User ID that meets this requirement and another person with the same User ID does not meet the requirement then we need to disregard these users. So in this query the answer would be 2. which would be Steve & Rich. We cannot count Brian as both Gary & Stephen have logged in and they have the same user ID. Hope this makes sense.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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