+ Reply to Thread
Results 1 to 9 of 9

Count Unique Values based on multiple criterias

  1. #1
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Kuching, SARAWAK
    MS-Off Ver
    Excel 2010
    Posts
    116

    Count Unique Values based on multiple criterias

    Hi Excel Experts,

    I found this awesome formula from below URL:-

    http://www.mrexcel.com/forum/excel-q...nge-cells.html

    The contributed formula counts how many unique values in rows and omits errors and blanks. However, this only applicable for one column. What I'm trying to achieve is something extra. I need to calculate how many Users (based on User ID) that access a system on the same date. So, my count must consider the date as well before populates output. Below is my data:-

    UserID Date
    V03751X 14/3/2016
    V03751X 15/3/2016
    V04046X 14/3/2016
    V03658X 15/3/2016
    V03751X 14/3/2016
    V03752X 14/3/2016

    So, on date 14/3/2016, the result should be 3 and on 15/3/2016 should be 2.

    I tried below, but returned error or '0' :-

    =SUM(IF(1-ISERROR($J$16:$J$21),IF($J$16:$J$21<>"",1))/COUNTIFS($K$16:$K$21,$K21,$J$16:$J$21,$J$16:$J$21&""))
    with Ctrl+Shift+Enter

    =SUM(IF(FREQUENCY(IF(1-ISERROR($J$16:$J$21),IF($J$16:$J$21<>"",AND(MATCH(K16,$K$16:$K$21,0),MATCH("~"&$J$16:$J$21,$J$16:$J$21&"",0)))),ROW($J$16:$J$21)-ROW($J$16)+1),1))
    with Ctrl+Shift+Enter

    Appreciate your expertise.

    Thank you in advance.

    DZ

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count Unique Values based on multiple criterias

    Try this one with Ctrl Shift Enter.

    =SUM(IFERROR(1/COUNTIFS($J$16:$J$21,$J$16:$J$21&"",$K$16:$K$21,$K$16:$K$21,$K$16:$K$21,$K21),0))

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,254

    Re: Count Unique Values based on multiple criterias

    Jason:

    typo in Formula ??


    =SUM(IFERROR(1/COUNTIFS($J$16:$J$21,$J$16:$J$21&"",$K$16:$K$21,$K$16:$K$21,$K$16:$K$21,$K16),0))

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count Unique Values based on multiple criterias

    Should work either way, John.

    I based mine on the criteria in the OP's first formula, where it looks like you based yours on the second.

    Hopefully at least one of us has it right.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,254

    Re: Count Unique Values based on multiple criterias

    Jason,
    You are right: when I tried it, I put the formula in K16 and dragged down so K21 was incremented and naturally gave wrong result! My apologies.

    John

  6. #6
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Kuching, SARAWAK
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Count Unique Values based on multiple criterias

    Hi http://www.excelforum.com/member.php?u=913270

    I've tested your formula. It works awesomely if there is no data error or blanks between the rows. So, I added a little bit and came out with the below:-

    with Ctrl+Shift+Enter

    =SUM(IFERROR(IF(1-ISERROR($J$16:$J$21),IF($J$16:$J$21<>"",1))/COUNTIFS($J$16:$J$21,$J$16:$J$21&"",$K$16:$K$21,$K$16:$K$21,$K$16:$K$21,$K16),0))

    Thank you so much for your time and expertise, JohnTopley!

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,254

    Re: Count Unique Values based on multiple criterias

    @Dahlia,
    Thanks are due to Jason not me.

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count Unique Values based on multiple criterias

    Quote Originally Posted by JohnTopley View Post
    Jason,
    You are right: when I tried it, I put the formula in K16 and dragged down so K21 was incremented and naturally gave wrong result! My apologies.

    John
    No need to apologise, John. On the assumption that dragging down is a requirement, you got it right, not me.

    @Dahlia

    Assuming that the errors or blanks would be in column J, with a date in the same row of column K (this was the only thing I found which made the original suggestion fail).

    Give this one a go, Shift Ctrl Enter again.

    =SUM(IFERROR(SIGN(LEN($J$16:$J$23))/COUNTIFS($J$16:$J$23,$J$16:$J$23&"",$K$16:$K$23,$K$16:$K$23,$K$16:$K$23,$K16),0))

    It looks like it works correctly, but I've only done a quick test.

  9. #9
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Kuching, SARAWAK
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Count Unique Values based on multiple criterias

    Hi jason.b75,

    Your formula works perfectly! Neat, short and concise! Tqvm!! Yeayy!

+ 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. Count unique values based on multiple criteria
    By Jonathan11235 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-19-2015, 10:02 PM
  2. Count Unique Values based on different Criterias
    By Morwick in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-03-2015, 04:49 PM
  3. [SOLVED] Count unique values based multiple criterias
    By Petter120 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2014, 03:07 AM
  4. [SOLVED] count unique values across multiple sheets based on date
    By roninn75 in forum Excel General
    Replies: 8
    Last Post: 02-25-2014, 05:20 PM
  5. [SOLVED] How to count unique values using multiple criterias
    By Charmymay in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-07-2013, 11:27 AM
  6. how to count unique values in excel based on multiple criteria
    By IDBUGM in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-15-2006, 12:10 PM

Tags for this Thread

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