+ Reply to Thread
Results 1 to 7 of 7

How to count unique values using multiple criterias

  1. #1
    Registered User
    Join Date
    01-08-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    21

    How to count unique values using multiple criterias

    Hello,

    I've tried various formulas to count the number of unique values, but I haven't found one that accounted for my second criteria. What I'm trying to do is to count the number of unique Type #'s but only for the ones that will be included. I hope my request makes sense, but if not just let me know. Thanks.
    Attached Files Attached Files
    Regards, Charms

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,233

    Re: How to count unique values using multiple criterias

    do you want to count the types - but only when yes

    i have put all the types in column D (you can do this with an array formula if required)
    and then used
    =COUNTIFS($A$2:$A$19,D2,$B$2:$B$19,"yes")
    in E2

    see attached
    Attached Files Attached Files

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to count unique values using multiple criterias

    Try this array formula**:

    =SUM(IF(FREQUENCY(IF(B2:B19="Yes",MATCH(A2:A19,A2:A19,0)),ROW(A2:A19)-ROW(A2)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    01-08-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: How to count unique values using multiple criterias

    Thank you bot @etaf and @TonyValko.

    @etaf I'm sorry I guess I wasn't uber clear. I meant to ask for a formula that I could calculate the total frequency in one cell as upposed to one cell for each Type #.

    @TonyValko your formula worked perfectly. Would it be possible to add one more criteria? I was hoping to have my array be a column, which means there may be blank cells. What I'd like is that if there are any blank cells it would just be ignored and not counted.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to count unique values using multiple criterias

    To account for empty cells in column A:

    =SUM(IF(FREQUENCY(IF(B2:B19="Yes",IF(A2:A19<>"",MATCH(A2:A19,A2:A19,0))),ROW(A2:A19)-ROW(A2)+1),1))

    Still array entered.

  6. #6
    Registered User
    Join Date
    01-08-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: How to count unique values using multiple criterias

    Thanks @TonyValko that did the trick!

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to count unique values using multiple criterias

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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