+ Reply to Thread
Results 1 to 2 of 2

Count uniques with several conditions

  1. #1
    Registered User
    Join Date
    03-12-2021
    Location
    Gothenburg
    MS-Off Ver
    365
    Posts
    10

    Question Count uniques with several conditions

    Hello,

    My situation:
    I have a list of companies that receives investment from different investors in different time periods. The timeperiod are classified into different "Rounds" and the investors are classified into A and B type of investors. I want to calculate number of unique investors that invest in Company X in Round Y given the investor classification. I have achieved this (with a lot of help from the forum) in columns J-K.

    My Problem:
    I have undisclosed investors in the data-set, which are treated as one investor, e.g. if we have one undisclosed investors in round 1 and 3 in round 2 - only the one in round 1 will be treated as a unique investor - I want to change this. I want to create the following rules for the Undisclosed investors:
    1. If equal number of Undisclosed Investor of type X are found in round Y+1 as in Y, it shall be assumed that these undisclosed investors are the same and hence no new Investors shall be counted.
    2. Example: If we have 2 Undisclosed investors of Investor type A in round 1 for company X, and 3 in round 2. The difference (3-2) shall be counted as a new investor.

    Columns L, N-O is where I wish to fill in my data. I have given a manual example of column L with comments of the rationale in column M.

    Big thank you in advance for the help - I have struggled to find a solution.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,554

    Re: Count uniques with several conditions

    This seems to me to be yielding the correct results.
    Column N is populated using: =IF(AND(C5="Undisclosed",I5="A"),MAX(0,COUNTIFS(B$5:B5,B5,C$5:C5,"Undisclosed",D$5:D5,D5,I$5:I5,"A")-SUMIFS(N$4:N4,B$4:B4,B5,C$4:C4,"Undisclosed",I$4:I4,"A")),0)
    Column O is populated using: =IF(AND(C5="Undisclosed",I5="B"),MAX(0,COUNTIFS(B$5:B5,B5,C$5:C5,"Undisclosed",D$5:D5,D5,I$5:I5,"B")-SUMIFS(O$4:O4,B$4:B4,B5,C$4:C4,"Undisclosed",I$4:I4,"B")),0)
    Column L is populated using: =SUM(N5:O5)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Count uniques matched on range, but if match reference is twice, count twice
    By AndyJr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-27-2019, 10:14 AM
  2. Count Uniques
    By tiger01 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2014, 02:00 AM
  3. Count uniques within duplicates
    By ckramer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2013, 07:06 PM
  4. Count Uniques
    By John Bates in forum Excel General
    Replies: 13
    Last Post: 10-12-2010, 01:57 PM
  5. Count Uniques with 2 criterias
    By ROYW1000 in forum Excel General
    Replies: 2
    Last Post: 11-03-2008, 05:48 PM
  6. count uniques
    By prasjohn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2008, 01:34 PM
  7. Count uniques, 2 columns
    By krozar in forum Excel General
    Replies: 4
    Last Post: 11-16-2006, 04:48 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1