Page 5 of 5 FirstFirst ... 345
Results 41 to 46 of 46

Thread: Formulas to count including Unique Numbers

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

    Default Re: Formulas to count including Unique Numbers

    If the possible values in E2:E14 are only REL and AD, as shown in your data sample, all you have to do is delete the condition IF(E2:E14 = "REL" from the formulas.

    M.
    Last edited by Marcelo Branco; Aug 1st, 2019 at 08:20 AM.

  2. #42
    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
    If the possible values in E2:E14 are only REL and AD, as shown in your data sample, all you have to do is delete the condition IF(E2:E14 = "REL" from the formulas.

    M.
    Hi M,

    Thanks for the reply.

    In my actual data there are numerous values in E2:E14. I just tried to keep it as simple as possible when I was getting all the formulas to work.

    Thanks

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

    Default Re: Formulas to count including Unique Numbers

    To use an OR condition in an array formula you should add the conditions, for example

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

    M.

  4. #44
    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 worked a treat.

  5. #45
    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
    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.
    Hi M,

    Quick query which I cannot figure out.

    Everything is working fine however I've been asked to provide additional data now which I cannot get the formula to work for.

    So
    C$2:C$14<>"" part of the formula returns any cells from C2 to C14 that are blank. I also need to add column B2 to B14 that are also blank. I know on my original spreadsheet they had names in but now in my master spreadsheet they have different data. I've tried the following but it does not return the correct data :-
    IF((B$2:B$14<>"")+(C$2:C$14<>"")+(D$2:D$14="Yes").

    Any ideas what the issue is ?

    Thanks

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

    Default Re: Formulas to count including Unique Numbers

    Graham,

    This thread is getting too long...
    New data? I suggest you create a new thread - new data sample along with expected results.

    M.

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
  •