+ Reply to Thread
Results 1 to 13 of 13

Unique Values Formula Based on Criteria

  1. #1
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Unique Values Formula Based on Criteria

    Good afternoon,

    I have watched the following video and needing additional assistance:

    https://www.youtube.com/watch?v=WfRgnebu6aY

    I am attemping to count unique values based on 2 criteria. My issue is when adding an and within the inner most if statement my formula appears to not be working. Please advise what I am missing with this formula.

    {=IFERROR(SUM(IF(FREQUENCY(IF(AND($G$2:$G$59=$B64,$E$2:$E$59=$C$62),MATCH($A$2:$A$59,$A$2:$A$59,0)),ROW($A$2:$A$59)-ROW($A$2)+1),1)),0)}

    Thanks for your help in advance.

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Unique Values Formula Based on Criteria

    Try to concatenate multiple IFs for the conditions instead of using AND. For an example look here.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Re: Unique Values Formula Based on Criteria

    Thank you for the response! That worked wonderfully. My formula has now been updated to the below. Any idea as to why that method works as opposed to using an AND?

    {=IFERROR(SUM(IF(FREQUENCY(IF($G$2:$G$59=$B$64,IF($E$2:$E$59=C$62,MATCH($A$2:$A$59,$A$2:$A$59,0))),ROW($A$2:$A$59)-ROW($A$2)+1),1)),0)}

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Unique Values Formula Based on Criteria

    Quote Originally Posted by zmster2033 View Post
    Any idea as to why that method works as opposed to using an AND?
    Because AND(...) returns a single result where you need an array of results.

    Why are you using IFERROR?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Unique Values Formula Based on Criteria

    Try this... New one..
    =IFERROR(SUM(IF(FREQUENCY(IF(COUNTIFS($B64,$G$2:$G$59,$C$62,$E$2:$E$59),MATCH($A$2:$A$59,$A$2:$A$59,0)),ROW($A$2:$A$59)-1),1)),0)
    It can be more better if provide a workbook..
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Unique Values Formula Based on Criteria

    The nested IF version is faster to calculate than the COUNTIFS version.

    The bigger the range the bigger the difference.

  7. #7
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Re: Unique Values Formula Based on Criteria

    Good call out. I have removed the IFERROR as it is redundant.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Unique Values Formula Based on Criteria

    Good deal. Thanks for the feedback!

  9. #9
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Unique Values Formula Based on Criteria

    Hi Tony...
    You have said that Nested Ifs are always faster to use than countif version..
    I request you to explain me the reason behind that..
    Is there any article on that.. if yes, then please provide a link as well....

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Unique Values Formula Based on Criteria

    There is calculation timer code here:

    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    The author of the code is a very highly respected expert on Excel efficiency.

    I do this kind of testing quite often.

    The best way to test is get an average of several timings. I usually get the average of 6 timings on increasingly larger ranges.

    For example, time 100 rows, then 1,000 rows, then 10,000 rows, then 50,000 rows, etc., etc.

  11. #11
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Unique Values Formula Based on Criteria

    Hi Tony..
    I read the article.. simply amazing..
    but do agree with this paragraph in context of your "Nested ifs" statement..?

    SUMIFS, COUNTIFS, and AVERAGEIFS
    Excel 2007 has three new functions that you can use to SUM, COUNT, or AVERAGE using multiple criteria. In earlier versions of Excel, you had to use slow-calculating, hard-to-understand array formulas or SUMPRODUCT to use multiple criteria. The new functions are easy to use and fast to calculate.
    SUMIFS(sum_range, criteria_range1, criteria1 [,criteria_range2, criteria2…])
    COUNTIFS(criteria_range1, criteria1 [,criteria_range2, criteria2…])
    AVERAGEIFS(average_range, criteria_range1, criteria1 [,criteria_range2, criteria2…])
    These functions handle full column references ($A:$A) very efficiently by using special handling for the empty cells. The criteria that evaluates text cells can use the wildcard characters * (any set of characters) and ? (any single character). Because these functions are so much faster to calculate than equivalent array formulas, you should use them to replace your array formulas wherever possible.

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Unique Values Formula Based on Criteria

    I think you're missing the intended use of those functions.

    He's saying:

    Instead of using an array formula like this:

    =SUM(IF(A1:A10="X",IF(B1:B10="Y",IF(C1:C10="Z",D1:D10))))

    Use this normally entered formula:

    =SUMIFS(D1:D10,A1:A10,"X",B1:B10,"Y",C1:C10,"Z")

    For the application of counting unique entries with conditions the nested IF version is faster to calculate than the COUNTIFS version.

    Use the timer code and see for yourself.
    Last edited by Tony Valko; 09-07-2014 at 09:24 PM.

  13. #13
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Unique Values Formula Based on Criteria

    Okay. Tony..
    Thanks for clarifying..
    I will definitely try it..

+ 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. Unique values between dates based on criteria
    By Philipdjhd in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-21-2014, 01:58 AM
  2. [SOLVED] Sum Unique Values Based on Other Column Criteria
    By jfist85 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-06-2013, 03:21 PM
  3. Countifs unique values based on two criteria
    By shaz0503 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-06-2012, 08:48 PM
  4. Counting Unique Values based on several criteria
    By GowHow in forum Excel General
    Replies: 2
    Last Post: 04-25-2012, 10:16 PM
  5. Count unique values based on several criteria
    By evilgrin in forum Excel General
    Replies: 2
    Last Post: 10-10-2010, 03:50 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