+ Reply to Thread
Results 1 to 3 of 3

Count Unique values with conditions

  1. #1
    Registered User
    Join Date
    04-30-2019
    Location
    Prague
    MS-Off Ver
    365
    Posts
    2

    Count Unique values with conditions

    I have a issue with this formula and i was looking everywhere, but i was not able to figure it out. What i want to do with this formula is after the code is changed i need to know, how many unique models was used for this changed code

    Formula is working only without F column condition, that means, i'm not able to say how many unique models i have for one code.

    E column - CODE
    015632360000
    003550080000
    004510600000
    003506990680
    003506990680
    003506990680

    F column- MODEL
    CUA32
    CUC31
    CUN06
    CUF20
    CUF20
    CUF19

    G column- Date
    17.4.2019 0:00
    17.4.2019 0:00
    17.4.2019 0:00
    17.4.2019 0:00
    17.4.2019 0:00
    17.4.2019 0:00

    I column - Date of change
    11.4.2019
    18.1.2019
    18.1.2019
    18.1.2019
    18.1.2019
    18.1.2019

    J column-changed CODE
    003506990680
    003572610000
    003582900000
    015404890000
    003411090000
    000000156204

    Solution should be for changed CODE 003506990680 2, because for this code were used two models CUF20 and CUF19

    =SUMPRODUCT(1/COUNTIFS($E$2:$E$16502;J2;$G$2:$G$16502;"<="&I2+30; $G$2:$G$16502; ">"&I2;$F$2:$F$16502;$F$2:$F$16502))

    Do you have any suggestions ? Thank you very much !
    Last edited by Klaudiii; 04-30-2019 at 07:51 AM.

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Count Unique values with conditions

    Hi,

    an attempt


    Please Login or Register  to view this content.
    to be confirmed with control+shift+enter

    Or if you would need to add another condition

    Please Login or Register  to view this content.
    again: riconfirmed with control+shift+enter.

    A sample file would be helpful.
    Last edited by canapone; 04-30-2019 at 08:14 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    04-30-2019
    Location
    Prague
    MS-Off Ver
    365
    Posts
    2

    Re: Count Unique values with conditions

    Thank you, it's working, but i don't know why, i was using control+shift+enter all the time, but i had always a problem with "E$1:E$16502=J2"

    I edited it, because i need it general

    =SUM(IF(FREQUENCY(IF((E$1:E$16502=J2)*(G$1:G$16502>I2)*(G$1:G$16502<=I2+30);MATCH(F$1:F$16502;F$1:F$16502;0));ROW($1:$16502));1))

    So one more time thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count unique values from a column with conditions
    By Fullspeeduws in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-30-2018, 08:19 AM
  2. Count unique values across sheets in a workbook that meet certain conditions
    By libramom814 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-29-2016, 08:41 AM
  3. How to count unique values in two ranges that meet conditions
    By akbermo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-18-2015, 08:22 AM
  4. [SOLVED] Count amount of unique values with multiple conditions
    By Melon6 in forum Excel General
    Replies: 17
    Last Post: 05-19-2015, 11:59 AM
  5. [SOLVED] Count only unique values with two conditions
    By Melon6 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-01-2015, 05:24 AM
  6. Count unique values with multiple conditions
    By liybpg in forum Excel General
    Replies: 6
    Last Post: 03-18-2015, 05:43 AM
  7. [SOLVED] count the number of unique values given certain conditions
    By eh308701 in forum Excel General
    Replies: 8
    Last Post: 05-31-2012, 11:48 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1