+ Reply to Thread
Results 1 to 8 of 8

Count unique values if 2 other criteria are met

  1. #1
    Registered User
    Join Date
    03-16-2010
    Location
    Chesterfield, England
    MS-Off Ver
    Excel 2016
    Posts
    19

    Count unique values if 2 other criteria are met

    Hi everyone.

    Firstly, apologies....I know there are plenty of threads about this issue online - after many many wasted hours, I simply can not get it working with my data.

    What I am after is a formula which counts the number of unique values from column B (Employer name), when 3 different criteria are met (year, type of event and event cancelled <>Y). For example, if IBM appeared twice in the raw data when "Year= C34" and "Type= B34" and "Cancelled <> Y" - 2 would be the result.

    I have data in one sheet, and the results in another. I am stuck with cells D34 in the 'Review of events' sheet, I can then use the same formula for all "Employer participation" stats.
    Employers.xlsx

    Any help would be greatly appreciated.
    Many thanks.

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

    Re: Count unique values if 2 other criteria are met

    Quote Originally Posted by sted999 View Post
    if IBM appeared twice in the raw data when "Year= C34" and "Type= B34" and "Cancelled <> Y" - 2 would be the result.
    If you're counting uniques wouldn't the result be 1?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

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

    Re: Count unique values if 2 other criteria are met

    I just downloaded your file and I can't make heads or tails out of it.

    Your description doesn't match what I see in the file.

  4. #4
    Registered User
    Join Date
    03-16-2010
    Location
    Chesterfield, England
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Count unique values if 2 other criteria are met

    Yes, correct the result would be 1 - good spot. My mind is frazzled with how long I've been working on this!

    This is just sample data so IBM may also hold a different type of event e.g. One-to-one, so this would be added up in cell D35.

  5. #5
    Registered User
    Join Date
    03-16-2010
    Location
    Chesterfield, England
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Count unique values if 2 other criteria are met

    Sorry I've not explained that very well. I want the TOTAL number of unique values to be placed in D34 if the other three criteria are met.

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Count unique values if 2 other criteria are met

    Hi

    in D6 and below to be confirmed with control+shift+enter

    =SUM(IF(FREQUENCY(IF(Data!$J$2:$J$38=C6,IF(Data!$I$2:$I$38=B6,IF(Data!$H$2:$H$38<>"Y",MATCH(Data!$B$2:$B$38,Data!$B$2:$B$38,0)))),ROW($A$2:$A$38)-1)>0,1,0))


    Please refer to yellow formulas

    Hope it helps
    Attached Files Attached Files
    Last edited by canapone; 09-30-2014 at 11:43 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  7. #7
    Registered User
    Join Date
    03-16-2010
    Location
    Chesterfield, England
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Count unique values if 2 other criteria are met

    Hi canapone.

    Thanks a lot for that, it's perfect! Works like an absolute dream.

    Thank you

  8. #8
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Count unique values if 2 other criteria are met

    Hi,

    thanks for providing feedback: I was a little lost in translating formulas from Italian settings.

    Greetings from Firenze

+ 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. Count unique values with specific criteria
    By kaz_jones in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2012, 03:56 PM
  2. Excel 2007 : Count Unique Values matching 3 criteria
    By Wayne M in forum Excel General
    Replies: 1
    Last Post: 04-03-2011, 07:02 PM
  3. Count Unique Values with criteria
    By mashley in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-08-2010, 02:00 PM
  4. Count unique values based on several criteria
    By evilgrin in forum Excel General
    Replies: 2
    Last Post: 10-10-2010, 03:50 PM
  5. Count unique values with criteria
    By benno87 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-03-2008, 11:13 PM

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