+ Reply to Thread
Results 1 to 17 of 17

Need to Count Unique Values down a column with multiple criteria

  1. #1
    Registered User
    Join Date
    08-19-2016
    Location
    NJ
    MS-Off Ver
    2011 - Mac
    Posts
    9

    Question Need to Count Unique Values down a column with multiple criteria

    Hi all,

    Currently I have a code set up to count all dates down a column based on 3 criteria throughout its row.

    =COUNTIFS($I$2:$I$9709,D3,$G$2:$G$9709,"<>Staff Only Detail",$B$2:$B$9709,"<>PED")

    This works just as I'd like it to, counting all "Employee Calls" based on the total number of calls that meet these 3 criteria.

    Now I need to calculate unique work days, meaning 1/1/2016, 1/1/2016, 1/2/2016, 1/3/2016 should retrieve a value of 3, instead of 4. However, I still need to use the same criteria from the COUNTIFS function above.

    Help is greatly appreciated, Thanks!
    Attached Files Attached Files
    Last edited by RMerckling; 08-21-2016 at 07:34 PM.

  2. #2
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Need to Count Unique Values down a column with multiple criteria

    Assuming your dates are in column A and employee calls are in column B:

    Please Login or Register  to view this content.
    This will return "Employee Calls_#" where # is your unique number.

    EDIT:nvm, misread it. I am returning 4 still. let me think about this one...

    How is your data sorted? If it's by date this isn't nearly as complicated as it could be...

    Also, a sample workbook would make this a lot easier to visualize.
    Last edited by TheN; 08-21-2016 at 05:43 PM.

  3. #3
    Registered User
    Join Date
    08-19-2016
    Location
    NJ
    MS-Off Ver
    2011 - Mac
    Posts
    9

    Re: Need to Count Unique Values down a column with multiple criteria

    Hey, thanks for the assistance.

    The calls aren't in a column at all, each row is a separate call.

  4. #4
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Need to Count Unique Values down a column with multiple criteria

    Mind attaching a sample workbook? Remember to remove sensitive information and replace with dummy info.

    This can be done by editing your post, then clicking GO ADVANCED. From there, scroll down to MANAGE ATTACHMENTS and attach your sample file.

  5. #5
    Registered User
    Join Date
    08-19-2016
    Location
    NJ
    MS-Off Ver
    2011 - Mac
    Posts
    9

    Re: Need to Count Unique Values down a column with multiple criteria

    Attached a sample. The two formulas to the right for Territory 1 & 2 I have for about 30 different territories and the only thing that changes is the territory it's searching for.

    I left blank the "new code" section which would need to search for those same criteria from the data set, but only pull the value if the date is unique.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need to Count Unique Values down a column with multiple criteria

    Hi,

    If I understand your request one way would be to add a helper column to your data which contained a COUNTIF function to count the number of dates. e.g. assuming the dates are column A

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and include the helper column as a 4th element in the COUNTIF function with 1 as the criteria
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  7. #7
    Registered User
    Join Date
    08-19-2016
    Location
    NJ
    MS-Off Ver
    2011 - Mac
    Posts
    9

    Re: Need to Count Unique Values down a column with multiple criteria

    Richard, I already have a working COUNTIF function that counts calls based on three criteria.

    I just need to find a way to only count unique values from a 4th column (Dates) while still using the same criteria from my original COUNTIF function.

    EDIT: I see what you're saying now, maybe "Unique" is the wrong word. I need to capture Distinct records to find out which days were worked by certain employees.

    This report consists of about 9,000 calls/rows spread throughout a quarter work-year (64 days).
    Last edited by RMerckling; 08-21-2016 at 07:03 PM.

  8. #8
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need to Count Unique Values down a column with multiple criteria

    You can use this Array formula :

    Please Login or Register  to view this content.
    To confirm array press Ctrl+Shift+Enter.

  9. #9
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Need to Count Unique Values down a column with multiple criteria

    It would work without helpers if there was a way to combine two cell values when refering to ranges, unfortunately, IDK how to do that (I tried concatenate and &).

    So, my guess is you can only do it with a helper column.

    I got it to work with a bunch of helper columns to help me visualize the results, but I don't think that's what you want.

    Attaching my workbook anyway...
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-19-2016
    Location
    NJ
    MS-Off Ver
    2011 - Mac
    Posts
    9

    Re: Need to Count Unique Values down a column with multiple criteria

    Doesn't seem to be working when I change the code up and add more dates.

    For example: I added 1/3/2016 and 1/4/2016 with with criteria the code is looking for but it doesn't pick them up.

    I'm not sure what the first criteria is saying for range D2:D11, then the criteria is D2:D11?

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

    Re: Need to Count Unique Values down a column with multiple criteria

    You should have included what results you expect.

    For the count of unique dates where:

    Column A <> PED
    Column B <> 3
    Column C = 1

    This array formula** entered in G2:

    =SUM(IF(FREQUENCY(IF((A2:A11<>"PED")*(B2:B11<>3)*(C2:C11=1),D2:D11),D2:D11),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Result = 2
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need to Count Unique Values down a column with multiple criteria

    Quote Originally Posted by RMerckling View Post
    Richard, I already have a working COUNTIF function that counts calls based on three criteria.

    I just need to find a way to only count unique values from a 4th column (Dates) while still using the same criteria from my original COUNTIF function.

    EDIT: I see what you're saying now, maybe "Unique" is the wrong word. I need to capture Distinct records to find out which days were worked by certain employees.

    This report consists of about 9,000 calls/rows spread throughout a quarter work-year (64 days).
    Using your example I meant put
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in E2 and copy down to E11, then your COUNTIFS function is

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need to Count Unique Values down a column with multiple criteria

    If I understand correctly try array-entering this in G2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    Then array this variation in H2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I get counts of 2 and 1 with the upload.
    Dave

  14. #14
    Registered User
    Join Date
    08-19-2016
    Location
    NJ
    MS-Off Ver
    2011 - Mac
    Posts
    9

    Re: Need to Count Unique Values down a column with multiple criteria

    Tony Valko,

    Works with my example and also with my actual code.

    Thank you so much.

    Thanks to everyone else too who attempted to assist, sorry for being a little confusing with the question asked, really appreciate the help!

  15. #15
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need to Count Unique Values down a column with multiple criteria

    Quote Originally Posted by Tony Valko View Post
    =SUM(IF(FREQUENCY(IF((A2:A11<>"PED")*(B2:B11<>3)*(C2:C11=1),D2:D11),D2:D11),1))
    This formula is working good I think. Great solution Tony and repped.

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

    Re: Need to Count Unique Values down a column with multiple criteria

    You're welcome. Thanks for the feedback!

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

    Re: Need to Count Unique Values down a column with multiple criteria

    Thank you!

+ 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 multiple criteria from same column
    By AlexandraT in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-23-2015, 12:46 PM
  2. Count Unique Values with multiple criteria
    By TotallyLost in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-04-2015, 10:24 AM
  3. Count Unique Values for Multiple Criteria
    By timjs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-03-2015, 10:09 AM
  4. Replies: 3
    Last Post: 05-23-2013, 07:50 PM
  5. Count Unique Values, but with multiple criteria
    By the northern monkey in forum Excel General
    Replies: 3
    Last Post: 02-02-2012, 07:45 AM
  6. Replies: 11
    Last Post: 06-09-2011, 03:17 PM
  7. [SOLVED] Count Unique Values with Multiple Criteria
    By JohnV in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-17-2006, 01:10 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