Formulas to count including Unique Numbers
Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 46

Thread: Formulas to count including Unique Numbers

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

    Default Re: Formulas to count including Unique Numbers

    What's the difference from the 2nd query? They look identical ...

    M.

  2. #22
    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,

    Probably a bad example but he slight difference here is query 2 gave me a number of unique User ID's base on the criteria. This time i need to count all the user ID's that meet the criteria.

    So query 2 result is 2 and they are Steve & Rich.

    This time the answer is also 2 and again it's Steve & Rich.

    However if I added another user under User id 3 with no logon data and UTC = "No" then this answer would be 3 but query 2 would still be 2 as query 2 was unique User ID's.

    Thanks

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

    Default Re: Formulas to count including Unique Numbers

    Try

    Array formula
    =SUM(IF(A2:A14<>"",--ISNA(MATCH(A2:A14,IF((C2:C14<>"")+(D2:D14="Yes"),A2:A14),0))))
    Ctrl+Shift+Enter

    M.

  4. #24
    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,

    Works a treat.

  5. #25
    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

    Next I would like to count the Unique User ID's based on the following criteria - Type="COL", Model=Rel",UTC="No". So in this case the number would be 4. User ID's 1,3,5,7.
    Then based on this information (
    Type="COL", Model=Rel",UTC="No")
    I need to count the total number of users. This would be 5 (Stephen, Brian,Steve,Simon & Darren)

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

    Default Re: Formulas to count including Unique Numbers

    First
    Array formula
    =SUM(IF(FREQUENCY(IF($D$2:$D$14="NO",IF(E2:E14="REL",IF(F2:F14="COL",MATCH(A2:A14,A2:A14,0)))),ROW(A2:A14)-ROW(A2)+1),1))
    Ctr+Shift+Enter

    Second
    Regular formula
    =COUNTIFS(D2:D14,"No",E2:E14,"REL",F2:F14,"COL")

    M.

  7. #27
    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, all working great.

    If i wanted to add another ask in the second regular formula to look down the Last Logon and then count all users, where would the additional C2:C15,<>"" go as i tried to add it but keep getting errors.

    =COUNTIFS(D2:D14,"No",E2:E14,"REL",F2:F14,"COL")

    Thanks.

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

    Default Re: Formulas to count including Unique Numbers

    =COUNTIFS(C2:C14,"<>",D2:D14,"No",E2:E14,"REL",F2:F14,"COL")

    M.

  9. #29
    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 these are working great. I've tried them on the full data set and get exactly what i need.

  10. #30
    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 Marcelo Branco View Post
    2nd query

    Array formula
    =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))
    Ctrl+Shift+Enter

    Does it work for you?

    M.
    M,

    Is it possible to tweak the above formula to provide a list instead of just a number ? So say I put the formula in M1 it would list in M1, M2 etc,, the results of the query ?

    Thanks

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
  •