+ Reply to Thread
Results 1 to 7 of 7

Possible Frequency Function to count unique values

  1. #1
    Registered User
    Join Date
    03-29-2018
    Location
    Los Angeles,California
    MS-Off Ver
    2010
    Posts
    8

    Possible Frequency Function to count unique values

    Hello,
    Hope I can get some assistance on this.

    I am trying to count unique values but only if amounts are >0. but in some cases, there are duplicates with different descriptions that need to be counted as 1 for the unique ID.

    Examples are in the attached

    1. If ID in Column C is >1 and Amounts in Column D is > 0 and the two descriptions are different, than count as 1 for that ID for True Count Column.

    2. If ID >1 and there are different Descriptions Column A, Count ID as -1 for Amount less than 0 and 1 for Amount greater than 0. True Count for ID in this case should be zero

    3. If duplicate ID and same Description. Count ID -1 for Amount less than 0 and 1 for Amount greater than 0. True count for ID should be zero.
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Possible Frequency Function to count unique values

    As I read the upload it appears you want a formula to conditionally sum common sections of column E.

    Is that correct?

    This is the basic formula in Column F. The addresses need to be edited due to the merged cells.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 04-23-2018 at 09:14 PM.
    Dave

  3. #3
    Registered User
    Join Date
    03-29-2018
    Location
    Los Angeles,California
    MS-Off Ver
    2010
    Posts
    8

    Re: Possible Frequency Function to count unique values

    Thanks, Column F was just a reference to help better understand what I am trying to achieve. Ideally I am looking for a solution to give me the results in Column E without merging cells. e.g. Can Cell E24 or E25 count as 1 but not both so when I pivot that ID count will be 1 instead of 2?

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Possible Frequency Function to count unique values

    Try:
    Formula for checking in E2:
    Please Login or Register  to view this content.
    Formula for counting in F2:
    Please Login or Register  to view this content.
    Drags both down
    Merge cells again if needed.
    Attached Files Attached Files
    Quang PT

  5. #5
    Registered User
    Join Date
    03-29-2018
    Location
    Los Angeles,California
    MS-Off Ver
    2010
    Posts
    8

    Re: Possible Frequency Function to count unique values

    Excellent! Exactly what I needed!!

    Thank you!!

  6. #6
    Registered User
    Join Date
    03-29-2018
    Location
    Los Angeles,California
    MS-Off Ver
    2010
    Posts
    8

    Re: Possible Frequency Function to count unique values

    Quote Originally Posted by bebo021999 View Post
    Try:
    Formula for checking in E2:
    Please Login or Register  to view this content.
    Formula for counting in F2:
    Please Login or Register  to view this content.
    Drags both down
    Merge cells again if needed.
    Hi Bebo021999,

    If the seq count is an odd number like 3, Im not getting a true count of 1.

    e.g. Description is the same ABCD11, ID is the same 6017254, Amount for first is -400, second 400, third is 400. True count should be 1.

    Thanks again!!

  7. #7
    Registered User
    Join Date
    03-29-2018
    Location
    Los Angeles,California
    MS-Off Ver
    2010
    Posts
    8

    Re: Possible Frequency Function to count unique values

    Got it now!!

    Thanks again!

+ 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] FREQUENCY of unique value and SUM of unique values based on criteria
    By dcathey5 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-05-2017, 11:02 AM
  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. count unique (frequency formula)
    By jw01 in forum Excel General
    Replies: 1
    Last Post: 01-12-2015, 10:00 PM
  4. [SOLVED] Extract unique values between two dates and count their frequency
    By Villalobos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2014, 10:01 AM
  5. [SOLVED] function to count unique values
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-10-2012, 12:48 PM
  6. COUNT Unique using Frequency
    By kw42chan in forum Excel General
    Replies: 7
    Last Post: 12-15-2011, 08:49 AM
  7. Function to count unique values?
    By Richard Buttrey in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-22-2005, 10: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