+ Reply to Thread
Results 1 to 6 of 6

FREQUENCY of unique value and SUM of unique values based on criteria

  1. #1
    Registered User
    Join Date
    06-30-2017
    Location
    Boise
    MS-Off Ver
    365
    Posts
    4

    FREQUENCY of unique value and SUM of unique values based on criteria

    Hello I have a table that I need to return the number of unique rates per person where values are greater than zero and if condition = 1 is true by 2 columns. I'm not getting expected results with array formula
    Please Login or Register  to view this content.
    and next question how to sum the unique rates.



    test_formula.pngtest_formula.xlsx

  2. #2
    Registered User
    Join Date
    06-30-2017
    Location
    Boise
    MS-Off Ver
    365
    Posts
    4

    Re: FREQUENCY of unique value and SUM of unique values based on criteria

    I am able to get the correct results with
    Please Login or Register  to view this content.
    except not sure how to include *([boli]=1) and *([onsite]=1) inside the FREQUENCY function.

  3. #3
    Registered User
    Join Date
    06-30-2017
    Location
    Boise
    MS-Off Ver
    365
    Posts
    4

    Re: FREQUENCY of unique value and SUM of unique values based on criteria

    I'm still stuck on this quest to SUM the unique values. Any help would be appreciated.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: FREQUENCY of unique value and SUM of unique values based on criteria

    In F2, try:

    =SUM(--(FREQUENCY(IF(([[ID ]]=[@[ID ]])*([rate]>0)*([onsite]=1)*([boli]=1),[rate]),[rate])>0),0) Ctrl Shift Enter

    In G2, try:

    =SUM(IF(FREQUENCY(IF(([[ID ]]=[@[ID ]])*([rate]>0)*([onsite]=1)*([boli]=1),MATCH([rate],[rate],0)),ROW([[ID ]])-ROW($B$2)+1),[rate])) Ctrl Shift Enter
    Last edited by 63falcondude; 07-05-2017 at 10:50 AM.

  5. #5
    Registered User
    Join Date
    06-30-2017
    Location
    Boise
    MS-Off Ver
    365
    Posts
    4

    Re: FREQUENCY of unique value and SUM of unique values based on criteria

    WORKED thanks!!!

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: FREQUENCY of unique value and SUM of unique values based on criteria

    You're welcome. Happy to help.

    If that solved your question, please mark this thread as SOLVED.

+ 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. Getting unique values based on Multiple Criteria
    By gclode in forum Excel General
    Replies: 3
    Last Post: 06-20-2016, 02:19 PM
  2. Using sum if frequency to count unique values based on certain criteria
    By Matthew_Smith86 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-08-2015, 05:27 AM
  3. List Unique Values Based On Criteria
    By tangmere.milli in forum Excel General
    Replies: 5
    Last Post: 03-16-2015, 09:49 AM
  4. [SOLVED] Count unique entries based off unique criteria in another column
    By hambly in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-11-2014, 03:33 PM
  5. Counting Unique Values based on several criteria
    By GowHow in forum Excel General
    Replies: 2
    Last Post: 04-25-2012, 10:16 PM
  6. Frequency of unique values meeting multiple criteria
    By systemsteve in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-30-2008, 09:50 AM
  7. Frequency of Unique Values
    By systemsteve in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2008, 08:46 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