Results 1 to 10 of 10

Thread: Excel Formula to give unique countif
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2011
    Posts
    440
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel Formula to give unique countif

    Hi,

    I'm trying to write a formula that says count unique values in the range of b3:B4 where bn3:bn4 does not equal no change.

    Any idea how to write this?

    Thanks,

    Mark

  2. #2
    Board Regular
    Join Date
    Jul 2011
    Posts
    440
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula to give unique countif

    Note - this also includes blank rows which I don't want to be included in the overall count.

    This formula works when there are no blanks, but breaks when there are blank rows:
    Code:
    =SUM(IF("No Change"<>$BN$3:$BN$277, 1/(COUNTIFS($BN$3:$BN$277, "<>No Change", $B$3:$B$277, $B$3:$B$277)), 0))

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Excel Formula to give unique countif

    Have a look here
    https://exceljet.net/formula/count-u...-with-criteria
    or here
    https://exceljet.net/formula/count-u...-with-criteria

    depending whether your values are text or numeric
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    468
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula to give unique countif

    does No Change have blanks? (assuming that is a named range, if not please explain what no change is)
    or just the BN3:BN277 range?
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  5. #5
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    468
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula to give unique countif

    oh nevermind. i see you mean that there is cell values that = "No Change" that you would like excluded. so maybe this? hard to tell without an example
    ctrl + shift + enter must be used

    Code:
    =SUM(--(FREQUENCY(IF(B3:B227<>"No Change",BN3:BN227),BN3:BN227)>0))
    Last edited by BlakeSkate; Sep 20th, 2019 at 03:44 PM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  6. #6
    Board Regular
    Join Date
    Jul 2011
    Posts
    440
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula to give unique countif

    Quote Originally Posted by BlakeSkate View Post
    does No Change have blanks? (assuming that is a named range, if not please explain what no change is)
    or just the BN3:BN277 range?
    Just the B range has potential blanks. BN has to equal not "No Changes" and B is the column I want to sum unique values. The B column sometimes has blanks and I don't want those included in the unique value count. I hope that makes more sense. Sorry for the confusion.
    Last edited by nirvehex; Sep 20th, 2019 at 03:49 PM.

  7. #7
    Board Regular
    Join Date
    Jul 2011
    Posts
    440
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula to give unique countif

    I tried entering this code, but it give me an N/A error.

    Code:
    =SUM(--(FREQUENCY(IF(B3:B277<>"",IF(BN2:BN277<>"No Change",MATCH(B3:B277,B3:B277,0))),ROW(B3:B277)-ROW(B3)+1)>0))
    Trying to count unique values in column B where BN DOES NOT equal No Change. Column B has some blanks which I don't want included in the unique count. Also, column BN has blanks which should get excluded as well.

    Thanks again!
    Last edited by nirvehex; Sep 20th, 2019 at 04:08 PM.

  8. #8
    Board Regular
    Join Date
    Jul 2011
    Posts
    440
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula to give unique countif

    Actually there was a typo. This code works:
    Code:
    =SUM(--(FREQUENCY(IF(B3:B277<>"",IF(BN3:BN277<>"No Change",MATCH(B3:B277,B3:B277,0))),ROW(B3:B277)-ROW(B3)+1)>0))
    Thanks Fluff for the referral site code!
    Last edited by nirvehex; Sep 20th, 2019 at 04:17 PM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Excel Formula to give unique countif

    Glad you figured it out & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    468
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula to give unique countif

    Quote Originally Posted by nirvehex View Post
    I tried entering this code, but it give me an N/A error.
    did you try the code i provided? it should do this without whatever you added
    i'll attach an example:

    BCD
    3144317793
    42752No Change
    52752No Change
    62752No Change
    73344533448
    8
    93344533448
    103344533448
    11744No Change
    1212341244

    Sheet1



    Array Formulas
    CellFormula
    D3{=SUM(--(FREQUENCY(IF(B3:B16<>"No Change",C3:C16),C3:C16)>0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

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
  •