+ Reply to Thread
Results 1 to 12 of 12

Count unique values based on multiple criteria

  1. 03-25-2020, 02:38 PM #1
    kv.singh
    kv.singh is offline
    Forum Contributor
    Join Date
    10-30-2019
    Location
    GUJARAT,INDIA
    MS-Off Ver
    MS OFFICE 2016
    Posts
    134

    Count unique values based on multiple criteria

    HOW TO COUNT UNIQUE VALUES USING 'FREQUENCY' OR ANY OTHER EXCEL FORMULA. I WANT TO COUNT UNIQUE VALUES IF CODE GIVEN IN COLUMN 'A'
    OF "BONOUS" SHEET SATISFIES BELOW MENTIONED CONDITIONS.

    01. >= VALUE GIVEN IN L3 AND <= VALUE GIVEN IN M3 OF BONOUS SHEET.
    02. COLUMN 'A' OF NB SHHEET IS EQUAL TO 'NN'
    02. CODE (i.e COLUMN 'A' OF SHEET 'BONOUS') MATCHES WITH 'CODE' GIVEN IN 'NB', SHEET.

    QUICK SOLUTION WILL BE HIGHLY APPRECIATED
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-25-2020, 06:22 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Count unique values based on multiple criteria

    I believe the formula in B3 would be:

    =COUNTIFS(NB!A:A, "NN", NB!I:I, ">=" & L3, NB!I:I, "<=" & M3, NB!J:J, A3)

    ...copied down as far as needed.

    But note that none of the data on sheet NB matched any of your criteria. I had to create some rows to test the formula works.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 03-26-2020, 12:29 AM #3
    kv.singh
    kv.singh is offline
    Forum Contributor
    Join Date
    10-30-2019
    Location
    GUJARAT,INDIA
    MS-Off Ver
    MS OFFICE 2016
    Posts
    134

    Re: Count unique values based on multiple criteria

    Thanks for your quick response. But my problem is I want count unique values(unique values in DOB column) which satisfies all above mentioned three conditions.
    Reply With QuoteRegister To Reply
  4. 03-26-2020, 06:28 AM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Count unique values based on multiple criteria

    OK, I can take another look at this tomorrow (4am here), but your data makes testing your requirements impossible.

    Please update your sample data to provide a workable test, including telling me what the expected result would be from your new better sample data / sample parameters.

    Thanks.
    Reply With QuoteRegister To Reply
  5. 03-26-2020, 08:06 AM #5
    kv.singh
    kv.singh is offline
    Forum Contributor
    Join Date
    10-30-2019
    Location
    GUJARAT,INDIA
    MS-Off Ver
    MS OFFICE 2016
    Posts
    134

    Re: Count unique values based on multiple criteria

    SORRY FOR INCONVENIENCE. REVISED xls FILE IS ATTACHED FOR YOUR READY REFERENCE.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-26-2020, 10:02 AM #6
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,784

    Re: Count unique values based on multiple criteria

    BONOUS


    B3=SUM(IF(FREQUENCY(IF(NB!$A$2:$A$1000="NN",IF(NB!$C$2:$C$1000>=BONOUS!$L$3,IF(NB!$C$2:$C$1000<=BONOUS!$M$3,IF(ISNUMBER(MATCH(NB!$G$2:$G$1000,BONOUS!$A3,0)),MATCH(NB!$F$2:$F$1000&BONOUS!$A3,NB!$F$2:$F$1000&BONOUS!$A3,0))))),ROW(BONOUS!$A$3:$A$1000)-ROW(BONOUS!$A$3)+1),1))

    control+shift+enter

    copy down
    Reply With QuoteRegister To Reply
  7. 03-26-2020, 01:16 PM #7
    kv.singh
    kv.singh is offline
    Forum Contributor
    Join Date
    10-30-2019
    Location
    GUJARAT,INDIA
    MS-Off Ver
    MS OFFICE 2016
    Posts
    134

    Re: Count unique values based on multiple criteria

    Thanks for reply but Error "this formula uses more levels of nesting than you can use in the current file format" is shown while executing this formula.
    Note: I am using Microsoft office-2016
    Reply With QuoteRegister To Reply
  8. 03-26-2020, 01:29 PM #8
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,784

    Re: Count unique values based on multiple criteria

    Attached file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 03-27-2020, 01:59 AM #9
    kv.singh
    kv.singh is offline
    Forum Contributor
    Join Date
    10-30-2019
    Location
    GUJARAT,INDIA
    MS-Off Ver
    MS OFFICE 2016
    Posts
    134

    Re: Count unique values based on multiple criteria

    Excellent.sir, thanks. My problem is solved. One more favour from you can you give me formula to sum "amount" column of 'nb' sheet for unique 'dob' for each 'code' in 'bonus' sheet.
    Take care. Be safe and stay safe.
    Reply With QuoteRegister To Reply
  10. 03-27-2020, 02:06 AM #10
    kv.singh
    kv.singh is offline
    Forum Contributor
    Join Date
    10-30-2019
    Location
    GUJARAT,INDIA
    MS-Off Ver
    MS OFFICE 2016
    Posts
    134

    Re: Count unique values based on multiple criteria

    Expected result file is attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 03-27-2020, 06:34 AM #11
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,784

    Re: Count unique values based on multiple criteria

    Bonous

    C3=SUM(IF(FREQUENCY(IF(NB!$A$2:$A$1000="NN",IF(NB!$C$2:$C$1000>=BONOUS!$L$3,IF(NB!$C$2:$C$1000<=BONOUS!$M$3,IF(ISNUMBER(MATCH(NB!$G$2:$G$1000,BONOUS!$A3,0)),MATCH(NB!$F$2:$F$1000&BONOUS!$A3,NB!$F$2:$F$1000&BONOUS!$A3,0))))),ROW(BONOUS!$A$3:$A$1000)-ROW(BONOUS!$A$3)+1),NB!$E$2:$E$1000))

    Control+shift+enter

    copy down
    Last edited by CARACALLA; 03-27-2020 at 06:37 AM.
    Reply With QuoteRegister To Reply
  12. 03-27-2020, 08:08 AM #12
    kv.singh
    kv.singh is offline
    Forum Contributor
    Join Date
    10-30-2019
    Location
    GUJARAT,INDIA
    MS-Off Ver
    MS OFFICE 2016
    Posts
    134

    Re: Count unique values based on multiple criteria

    Thanks for excellent guidence.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count Unique values in multiple worksheets based on certain criteria
    By Kyuss21 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-06-2019, 01:09 PM
  2. [SOLVED] SUMPRODUCT function count the Unique values based on Multiple criteria
    By savetrees in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2018, 04:03 AM
  3. [SOLVED] Count Unique Text Values Based on Multiple Criteria in Different Columns (inc. wildcard)
    By t4ms in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-16-2017, 07:11 AM
  4. Count unique values based on multiple criteria
    By Jonathan11235 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-19-2015, 10:02 PM
  5. [SOLVED] how to count unique values in excel based on multiple criteria
    By IDBUGM in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-15-2006, 12:10 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1