Page 4 of 5 FirstFirst ... 2345 LastLast
Results 31 to 40 of 46

Thread: Formulas to count including Unique Numbers

  1. #31
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,272
    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
    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
    Maybe this..

    M
    1
    List
    2
    3
    3
    6
    4


    Array formula in M2 copied down
    =IFERROR(INDEX(A$2:A$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH(A$2:A$14,IF((C$2:C$14<>"")+(D$2:D$14="Yes"),A$2:A$14),0)),MATCH(A$2:A$14,A$2:A$14,0)),ROW(A$2:A$14)-ROW(A$2)+1),ROW(A$2:A$14)-ROW(A$2)+1),ROWS(M$2:M2))),"")
    Ctrl+Shift+Enter

    M.

  2. #32
    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 a treat. Is there a way of extending this out so M2 shows "3", N2 shows "Steve". The more info i can get here the better for my situation.

    Thanks again.

  3. #33
    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.
    Hi M,

    As you have done for one of my other formulas, can you assist with also getting these into a list instead of just a number ?

    Thanks

  4. #34
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,272
    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
    Thanks M, works a treat. Is there a way of extending this out so M2 shows "3", N2 shows "Steve". The more info i can get here the better for my situation.

    Thanks again.
    All you have to do is changing
    =IFERROR(INDEX(A$2:A$14,...

    to
    =IFERROR(INDEX(B$2:B$14,...

    M.

  5. #35
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,272
    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,

    As you have done for one of my other formulas, can you assist with also getting these into a list instead of just a number ?

    Thanks
    See formula in post 31 - same idea

    Try to do it yourself - if you have problems come back here

    M.

  6. #36
    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,

    Tried it but it's not right.

    =IFERROR(INDEX(A$2:A$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($A$2:$A$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="no"),$A$2:$A$14),0)),MATCH($A$2:$A$14,$A$2:$A$14,0)),ROW($A$2:$A$14)-ROW($A$2)+1),ROW(A$2:A$14)-ROW(A$2)+1),ROWS(N$2:N2))),"")

    This gives me the User ID of 9 which is correct.

    But

    =IFERROR(INDEX(B$2:B$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($B$2:$B$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="No"),$B$2:$B$14),0)),MATCH($B$2:$B$14,$B$2:$B$14,0)),ROW($B$2:$B$14)-ROW($B$2)+1),ROW(B$2:B$14)-ROW(B$2)+1),ROWS(N$2:N2))),"")

    This gives me the name Trev but it should be Oliver as this is the name for User ID 9.

    Any ideas what i'm doing wrong ?

    Thanks


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

    Default Re: Formulas to count including Unique Numbers

    Your formula seems perfect to me. Trev and Oliver satisfy the conditions.

    M.

  8. #38
    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
    Your formula seems perfect to me. Trev and Oliver satisfy the conditions.

    M.
    Hi M,

    This isn't right.

    One of the earlier formulas was the following :-

    =SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A14,IF((C2:C14<>"")+(D2:D14="No"),A2:A14),0)),MATCH(A2:A14,A2:A14,0)),ROW(A2:A14)-ROW(A2)+1),1))

    The result for this was 1. This is because there is 1 generic user ID where UTC=Yes and Last Logon is Blank. This is correct. Next I wanted a list of the results instead of a number.

    This formula:-
    =IFERROR(INDEX(A$2:A$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($A$2:$A$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="no"),$A$2:$A$14),0)),MATCH($A$2:$A$14,$A$2:$A$14,0)),ROW($A$2:$A$14)-ROW($A$2)+1),ROW(A$2:A$14)-ROW(A$2)+1),ROWS(N$2:N2))),"")

    This gives me number 9 which is also correct as this is the user that meets the criteria on the above formula. What I tried to do with the following formula was pull the next cell to number 9 which was the name Oliver. However I get the name Trev instead but this person does not meet the criteria as their User ID is 4 and there is also another 4 that does not meet the criteria.

    =IFERROR(INDEX(B$2:B$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($B$2:$B$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="No"),$B$2:$B$14),0)),MATCH($B$2:$B$14,$B$2:$B$14,0)),ROW($B$2:$B$14)-ROW($B$2)+1),ROW(B$2:B$14)-ROW(B$2)+1),ROWS(O$2:O2))),"")

    So Trev should never be part of this formula as there are 2 x User ID 4's and one does not meet the criteria.

    Hope this makes sense.

    Thanks as always.


  9. #39
    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,

    All sorted, my error. Got it working.

    Thanks again.

  10. #40
    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
    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.
    Hi M,

    How can I add another part to this formula so when it looks down E2:E14 it counts both Rel & AD ?

    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
  •