+ Reply to Thread
Results 1 to 6 of 6

Counting Unique values with multiple criteria

  1. #1
    Registered User
    Join Date
    04-14-2012
    Location
    saudi arabia
    MS-Off Ver
    Excel 2007
    Posts
    15

    Counting Unique values with multiple criteria

    Hi All,

    I am trying to count unique values with multiple criteria.

    I used below Formula but I am getting #Value

    {=SUM(--(FREQUENCY(IF(AND(B18:B23=D17,A18:A23=C17),C18:C23),C18:C23)>0))}

    the table I am using for this example similar to below, once it is working will apply it to a much bigger table

    Date color value
    1-Jun Red 11
    1-Jun Red 22
    1-Jun Red 11
    1-Jun Blue 11
    2-Jun Red 22
    2-Jun Red 22
    2-Jun Blue 22
    2-Jun Blue 22

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Counting Unique values with multiple criteria

    Use this array formula:

    =SUM(IF(FREQUENCY(IF($A$18:$A$25=C$17,IF($B$18:$B$25=$D17,$C$18:$C$25)),IF($A$18:$A$25=C$17,IF($B$18:$B$25=$D17,$C$18:$C$25)))>0,1))

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    It's always better to post a sample workbook, by the way - that way we can see other problems that are undtectable from an unformatted table.
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Counting Unique values with multiple criteria

    =sum(--(frequency(if((b18:b25=d17)*(a18:a25=c17),c18:c25),c18:c25)>0))

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Counting Unique values with multiple criteria

    On second thoughts... a more generic formula (works with text and numbers) is (array entered):


    =SUM(IF(($C$17=$A$18:$A$25)*($B$18:$B$25=$D$17), 1/COUNTIFS($A$18:$A$25, $C$17, $B$18:$B$25, $D$17, $C$18:$C$25, $C$18:$C$25)), 0)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-14-2012
    Location
    saudi arabia
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Counting Unique values with multiple criteria

    Thank you All, that was really helpful

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Counting Unique values with multiple criteria

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. counting unique values with multiple criteria
    By freil in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 12-12-2016, 06:19 PM
  2. [SOLVED] Counting unique values with multiple criteria
    By f.bomb in forum Excel General
    Replies: 5
    Last Post: 08-13-2014, 05:00 AM
  3. [SOLVED] Counting Unique/Different Values based on multiple Criteria
    By jdodz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-22-2014, 05:41 PM
  4. Counting Unique Values on Multiple Criteria
    By buhwheet in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-10-2014, 01:32 AM
  5. Counting unique values that meet multiple criteria
    By msworkman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-08-2013, 11:15 PM
  6. [SOLVED] Counting Unique Values with Multiple Criteria
    By smwbuddy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-15-2013, 08:35 PM
  7. Counting Unique Values with Multiple Criteria
    By CELIA.NEFF in forum Excel General
    Replies: 10
    Last Post: 02-15-2012, 11:31 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