+ Reply to Thread
Results 1 to 3 of 3

Count Unique values in Column based on values in other columns

  1. #1
    Registered User
    Join Date
    01-19-2009
    Location
    earth
    MS-Off Ver
    2013
    Posts
    19

    Count Unique values in Column based on values in other columns

    Hope everyone is having a good Friday.

    I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

    My current formula in Summary tab D4:D19 is
    {=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
    +
    SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}

    This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

    The results in the pink highlighted cells (Summary column D) should be:
    Names starting with A - 3
    All others - 2

    I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

    Thanks in advance
    -D
    Attached Files Attached Files

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count Unique values in Column based on values in other columns

    You should be able to combine those formulas in one like this

    =SUM(IF(FREQUENCY(IF(Data!$I$3:$I$82<$E$1,IF((Data!$A$3:$A$82=$H$1)+(Data!$A$3:$A$82=$H$1),IF(Data!$C$3:$C$82=A4,Data!$I$3:$I$82))),Data!$I$3:$I$82),1))

    Note: you don't need the row functions, in fact they make the formula incorrect.

    That will count the number of different dates where the other conditions are met

    I'm not getting the same results as you suggest......do you want to count the date twice if it appears once for Y1 and once for Y2?
    Audere est facere

  3. #3
    Registered User
    Join Date
    01-19-2009
    Location
    earth
    MS-Off Ver
    2013
    Posts
    19

    Re: Count Unique values in Column based on values in other columns

    Thanks for cleaning that formula up. It helps a lot to be able to work through the issue i'm having.

    Yes, I want to count the number of days the data appeared in Y1 AND in Y2.

    Basically I'm looking to calculate the number of days an employee worked on Y1 and Y2 so that I can divide their total results by the number of days it took to obtain those results, giving me an average for results per day.

    What appears to have worked is: =SUM(IF(FREQUENCY(IF(Data!$I$3:$I$82<$E$1,IF(Data!$C$3:$C$82=A4,IF(Data!$A$3:$A$82=$I$1,Data!$I$3:$I$82))),Data!$I$3:$I$82),1))+SUM(IF(FREQUENCY(IF(Data!$I$3:$I$82<$E$1,IF(Data!$C$3:$C$82=A4,IF(Data!$A$3:$A$82=$H$1,Data!$I$3:$I$82))),Data!$I$3:$I$82),1))

    Array formula of course

    Basically, taking the changes you made to the original formula remove the error I was having, but still including the function twice, once for Y2 and once for Y1.

    Not sure why this worked where yours didn't, but bottom line is it does ... after I get another cup or two of coffee in me I might take another look and see what is going on there.

    THanks!
    -D
    Last edited by dmschave; 03-17-2014 at 02:20 PM.

+ 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] Find count of Unique or Duplicate Values based on Concatenated values in 2 columns
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-03-2014, 12:42 AM
  2. Replies: 1
    Last Post: 11-16-2013, 05:18 AM
  3. Count of unique values based on two columns of data
    By JodyMathis1973 in forum Excel General
    Replies: 8
    Last Post: 04-18-2012, 10:11 AM
  4. Count Unique Values based on 2 columns
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 33
    Last Post: 02-14-2012, 11:27 AM
  5. count unique values in columns based on date values
    By cclntn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2010, 06:16 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