+ Reply to Thread
Results 1 to 5 of 5

Count Unique Values With Multiple Conditions, Array Method

  1. #1
    Registered User
    Join Date
    11-10-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Count Unique Values With Multiple Conditions, Array Method

    Guys,

    I need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.

    I've attached the XL file for your review of the project. I appreciate your help.

    =SUM(IF(FREQUENCY(IF(('TX1'!$B$2:$B$15=B2)*('TX1'!$C$2:$C$15=C2)*('TX1'!$D$2:$D$15=D2),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0)),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0))>0,1))

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

    Re: Count Unique Values With Multiple Conditions, Array Method

    I think that formula works OK, it isn't dependent on the values being numeric - 1 is the correct result for each of those criteria, isn't it? What results do you expect?
    Audere est facere

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,908

    Re: Count Unique Values With Multiple Conditions, Array Method

    Are you sure the results in sheet N2 are correct?
    8-4-2014 has a result of 3
    In sheet N1 there is only 1 row with 8-4-2014
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

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

    Re: Count Unique Values With Multiple Conditions, Array Method

    Quote Originally Posted by popipipo View Post
    .....8-4-2014 has a result of 3.....
    Assuming the formula is correct then the date isn't part of the criteria - the formula counts the number of different dates (in column A) where the criteria in columns B, C and D are satisfied

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,908

    Re: Count Unique Values With Multiple Conditions, Array Method

    Maybe a pivot table is what you need?

+ 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] Count multiple conditions array only works with 1 value
    By bob33 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-19-2013, 12:57 PM
  2. [SOLVED] count the number of unique values given certain conditions
    By eh308701 in forum Excel General
    Replies: 8
    Last Post: 05-31-2012, 11:48 PM
  3. Count unique logs with multiple conditions of multiple sheets
    By Robert Bob in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-12-2007, 12:49 AM
  4. Advanced unique cell count with multiple conditions ... help!
    By Flystar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-24-2005, 10:50 PM
  5. Count Unique text based on multiple conditions
    By MAX258 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2005, 01:44 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