+ Reply to Thread
Results 1 to 7 of 7

SUM FREQUENCY formula to count distinct values w/ OR condition

  1. #1
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    SUM FREQUENCY formula to count distinct values w/ OR condition

    See attached workbook.

    Table with named ranges for each column. ID is numeric and may or may not have duplicates. Each condition column has values of 1 or 0 (my actual data set also has some that contain the word "Null" but I'm treating those as 0 and only counting the 1's, so it shouldn't be an issue).

    I have a working formulas to all 3 conditions with a 1, or 2 of the 3 conditions with a 1 and return the distinct number of IDs in cells F22, F24 and F26. What I'm needing is formula to calculate the distinct ID's with Cond3=1, and if either Cond1 OR Cond2 = 1. In other words, an "either or" instead of a "both and."

    Make sense?
    sample A.xlsx

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

    Re: SUM FREQUENCY formula to count distinct values w/ OR condition

    As it appears you understand the principles of the formula, and how to create arrays.

    Use * for AND conditions or + for OR conditions, remembering extra parenthesis where required.

    {=SUM(--(FREQUENCY(IF((ID<>"")*(Cond3=1)*((Cond2=1)+(Cond1=1)),MATCH(""&ID,ID&"",0)),ROW(ID)-ROW($A$2)+1)>0))}

  3. #3
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: SUM FREQUENCY formula to count distinct values w/ OR condition

    Yes, I understand them, but I'm just forgetful as I haven't worked with them in about a year... ah, the plus sign!!! Of course!! ...thanks!!

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,875

    Re: SUM FREQUENCY formula to count distinct values w/ OR condition

    Try this formula. Array-entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit Oops. Didn't refresh before posting.
    Dave

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

    Re: SUM FREQUENCY formula to count distinct values w/ OR condition

    Quote Originally Posted by HeyInKy View Post
    just forgetful as I haven't worked with them in about a year...
    That's understandable, I forget things I haven't worked with since breakfast

  6. #6
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: SUM FREQUENCY formula to count distinct values w/ OR condition

    Quote Originally Posted by FlameRetired View Post
    Try this formula. Array-entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit Oops. Didn't refresh before posting.
    "A" for effort and getting it correct though!!! Better late than never?? ...Thanks!

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,875

    Re: SUM FREQUENCY formula to count distinct values w/ OR condition

    You're welcome. (blush)

+ 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. Replies: 0
    Last Post: 04-27-2015, 03:32 PM
  2. [SOLVED] Finding distinct values across multiple columns - FREQUENCY array?
    By brokenbiscuits in forum Excel General
    Replies: 4
    Last Post: 01-12-2015, 09:06 AM
  3. Count Distinct Values
    By trevordsmith in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-02-2014, 09:21 PM
  4. [SOLVED] Count Conditional Distinct Values
    By Gos-C in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2013, 05:41 AM
  5. [SOLVED] Count Distinct Values
    By djfatboyfats in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-20-2012, 07:39 AM
  6. Formula to count distinct values WITH a lookup comparison
    By rgunning06 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2009, 05:30 PM
  7. Count Distinct Values?
    By bill_morgan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-26-2005, 10:06 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