+ Reply to Thread
Results 1 to 2 of 2

Counting unique numbers with criteria

  1. #1
    Registered User
    Join Date
    02-08-2018
    Location
    London
    MS-Off Ver
    2019
    Posts
    35

    Counting unique numbers with criteria

    Hi All -

    I'm struggling with creating a formula. The data has the following columns:
    Membership number, Member name, Active(Yes/No), Group, consultant1, consultant2, consultant3

    Note, there can be multiple member names for each membership number.

    I need a formula that will count the total number of unique memberships for a consultant, when that consultant is listed in either consultant1, consultant2, consultant3, and when that membership is listed as active.

    =SUM(--(FREQUENCY(IF((consultant1=E2)*(Active="Yes"),Membership number),Membership number)>0))+(--(FREQUENCY(IF((consultant2=E2)*(Active="Yes"),Membership number),Membership number)>0))+(--(FREQUENCY(IF((consultant3=E2)*(Active="Yes"),Membership number),Membership number)>0))

    where 'E2' is a cell that references the consultant. But it isn't delivering accurate results when I check the data manually. Is there a better/shorter way?

    TIA!

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Counting unique numbers with criteria

    A pivot table might be able to do this painlessly.

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

+ 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. Replies: 8
    Last Post: 05-26-2016, 06:00 PM
  2. [SOLVED] Counting Unique numbers in a list
    By Notters in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-23-2013, 12:38 PM
  3. Counting unique numbers
    By aresquare1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2013, 07:18 AM
  4. Counting unique criteria?
    By theThirdMan in forum Excel General
    Replies: 3
    Last Post: 11-06-2012, 06:41 PM
  5. Counting Unique Numbers in List
    By ssmith0011984 in forum Excel General
    Replies: 1
    Last Post: 03-10-2010, 02:11 PM
  6. Counting unique numbers
    By maxnpj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-13-2009, 11:42 PM
  7. [SOLVED] Counting Unique Values Given Criteria
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

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