+ Reply to Thread
Results 1 to 7 of 7

Count Unique Values based on different Criterias

  1. #1
    Registered User
    Join Date
    02-02-2015
    Location
    US
    MS-Off Ver
    2013
    Posts
    2

    Question Count Unique Values based on different Criterias

    Hello all, so i have read the different posts on the forum about this same topic but i found the example given to simple to apply to more complex data sets.
    For example take a look at this data set.

    Account_Number Tran_Amount SIC Year Month
    1234567890 $98 5413 2009 1
    1234567891 $33 5812 2006 2
    1234567892 $37 5812 2009 1
    1234567890 $41 5411 2008 3
    1234567894 $46 5812 2009 1
    1234567892 $51 5413 2007 8
    1234567896 $57 5411 2009 1
    1234567897 $63 5413 2006 5
    1234567890 $70 5411 2009 1
    1234567899 $78 5812 2009 1

    I would like to know how many Account numbers are there that have a SIC of 5812 in year 2009 and month 1. The answer should be 3.
    Also, i wood like to know how many Account numbers had transactions in 2009 month 1 for all SIC but 5411. The answer should be 4.

    I believe the formula that should be used here is =SUM(IF(FREQUENCY(
    but i cannot figure out of to do it properly.

    Your help is greatly appreciated.

    Thank you,

    Morwick


    ps: ( i tried to create a table but i couldn't, 5 columns:Acc #, Tran-Amt, SIC, Year, Month )

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Count Unique Values based on different Criterias

    A simple COUNTIFS() should suffice instead of SUM(IF(FREQUENCY(.

    See the attached workbook for a solution.

    For your second inquiry, your input into G2 should be <>5411.

    Hope this helps!
    Attached Files Attached Files
    Last edited by mcmahobt; 02-02-2015 at 02:45 PM.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count Unique Values based on different Criterias

    If your data is in columns A to E then, if account numbers are actual numbers you could use this formula

    =SUM(IF(FREQUENCY(IF(C2:C10=5812,If(D2:D10=2009,IF(E2:E10=1,A2:A10))),A2:A10),1))

    For any type of data in column A this longer version should work

    =SUM(IF(FREQUENCY(IF(C2:C10=5812,If(D2:D10=2009,IF(E2:E10=1,IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))))),ROW(A2:A10)-ROW(A2)+1),1))

    Both need to be confirmed with CTRL+SHIFT+ENTER

    Note: I'm assuming that all data is numeric in columns C, D and E - if that isn't the case then you might need quotes around the criteria values, i.e. "5812", "2009" and "1"
    Audere est facere

  4. #4
    Registered User
    Join Date
    02-02-2015
    Location
    US
    MS-Off Ver
    2013
    Posts
    2

    Re: Count Unique Values based on different Criterias

    Thx a lot guys, all the formulas you provided have been helpful. i have much to learn

  5. #5
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Count Unique Values based on different Criterias

    Quote Originally Posted by daddylonglegs View Post
    For any type of data in column A this longer version should work

    =SUM(IF(FREQUENCY(IF(C2:C10=5812,If(D2:D10=2009,IF(E2:E10=1,IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))))),ROW(A2:A10)-ROW(A2)+1),1))
    Daddylonglegs,

    Coming back to this for reference, I was wondering if you could provide some clarification. Do you actually need the -ROW(A2)+1 portion of your formula? Or will that negate itself? I like your application of this combination of sum,if,frequency vs. using a helper column, and am trying to wrap my head around it.

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count Unique Values based on different Criterias

    Quote Originally Posted by mcmahobt View Post
    Do you actually need the -ROW(A2)+1 portion of your formula?
    Hello mcmahobt,

    Yes, I think you do

    The idea is that the "bins" in FREQUENCY function are the same values that the MATCH function can generate, so MATCH(A2:A10,A2:A10,0) generates an array of values which are integers 1 to 9....and ROW(A2:A10)-ROW(A1)+1 generates this array for the bins

    {1;2;3;4;5;6;7;8;9}

    If those don't match then you may get incorrect results, e.g. assume all 9 rows match the criteria and that A2 and A10 contain "xyz" and A3:A9 all have "abc" - that should be a result of 2 - because there are 2 different values in column A (where criteria match) , but if you remove -ROW(A2)+1 you get an incorrect result - 1

    If you use ROW(A2:A10)-1 then that will work, but it makes the formula less robust - if I add rows in the worksheet at the top my range might change to A4:A10 but now ROW(A4:A10)-1 doesn't give me what I need, ROW(A4:A10)-ROW(A4)+1 will still give the correct bins array

  7. #7
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Count Unique Values based on different Criterias

    Makes much more sense. Thanks for taking the time to explain!

+ 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] Find count of Unique or Duplicate Values based on Concatenated values in 2 columns
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-03-2014, 12:42 AM
  2. [SOLVED] Count unique values based multiple criterias
    By Petter120 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2014, 03:07 AM
  3. [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
  4. [SOLVED] count unique values based on unique values
    By neetu.aggarwal in forum Excel General
    Replies: 13
    Last Post: 10-23-2012, 04:00 AM
  5. [SOLVED] Count unique values and create list based on these values
    By vipa2000 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM

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