+ Reply to Thread
Results 1 to 9 of 9

Counting Unique Values on Multiple Criteria

  1. #1
    Registered User
    Join Date
    03-09-2010
    Location
    Cerritos, CA
    MS-Off Ver
    Excel 2010
    Posts
    31

    Counting Unique Values on Multiple Criteria

    I've been faced with this dilemna for 3 days now. I have a table of data with duplicate information wherein I need to count each occurence of each duplicate in a single column only once based on 4 different criteria.

    Sample file is attached in .xlsx format.

    Blue columns represent the criteria and Yellow column represents what I'm counting. I've been using COUNTIFS based on the criteria, but it doesn't count unique values.

    Your help is appreciated.

    pt
    Attached Files Attached Files

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Counting Unique Values on Multiple Criteria

    Looks like 1 every time to me.

  3. #3
    Registered User
    Join Date
    03-09-2010
    Location
    Cerritos, CA
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Counting Unique Values on Multiple Criteria

    Quote Originally Posted by Bob Phillips View Post
    Looks like 1 every time to me.
    Thank you for the response. However, let me reiterate my dilema. I've attached a more comprehensive file of what I'm up against to help explain the matter.

    Please take a look when you have a moment....
    Attached Files Attached Files

  4. #4
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Counting Unique Values on Multiple Criteria

    Try this array formula

    =SUM(--(FREQUENCY(IF(($B$2:$B$1000=$M$4)*($D$2:$D$1000=$K$5)*($H$2:$H$1000=$L5),MATCH($I$2:$I$1000,$I$2:$I$1000,0)),ROW(INDIRECT("1:"&ROWS($I$2:$I$1000))))>0))

  5. #5
    Registered User
    Join Date
    03-09-2010
    Location
    Cerritos, CA
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Counting Unique Values on Multiple Criteria

    I'll give it a shot and let you know....Thanks again.

  6. #6
    Registered User
    Join Date
    03-09-2010
    Location
    Cerritos, CA
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Counting Unique Values on Multiple Criteria

    Worked like a charm. BIG formula. Thx Bob

  7. #7
    Registered User
    Join Date
    01-07-2014
    Location
    us
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Counting Unique Values on Multiple Criteria

    I, too, have been trying to get this formula to work for a similar issue with countifs and other tricks for days. This one worked on the first go! Yippeee!!! Awesome. Thank you so much!!!!!!

  8. #8
    Registered User
    Join Date
    01-09-2014
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Counting Unique Values on Multiple Criteria

    Quote Originally Posted by Bob Phillips View Post
    Try this array formula

    =SUM(--(FREQUENCY(IF(($B$2:$B$1000=$M$4)*($D$2:$D$1000=$K$5)*($H$2:$H$1000=$L5),MATCH($I$2:$I$1000,$I$2:$I$1000,0)),ROW(INDIRECT("1:"&ROWS($I$2:$I$1000))))>0))
    Hi Bob
    I'd like to ask a question that deals with counting blanks, instead of summing. I have column of data and I am counting the blank cells by using Countblank(A1:A101). Next month I will have more rows of data, say 200 rows and I'd like my formula to count the blanks to the last non-blank value in the column. Is there a way to do that in a formula instead of VB?
    Thanks
    Joe (new member to list)

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,160

    Re: Counting Unique Values on Multiple Criteria

    powersj,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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