+ Reply to Thread
Results 1 to 9 of 9

How to Count Unique Values with COUNTIFS?

  1. #1
    Registered User
    Join Date
    07-26-2011
    Location
    phoenix, arizona
    MS-Off Ver
    Excel 2007
    Posts
    53

    How to Count Unique Values with COUNTIFS?

    Hello Forum,

    I am seeking assistance on how to count unique job roles on different rows when 1 of 3 columns has the word "Yes".

    On the attached worksheet, I have roles like Developer, QA, Scrum, etc. On a project, 1 or more resources may be assigned to perform that role. The same resource name can be on multiple rows, but I only want to count the resource name 1 time "if" they have a "Yes" in any 1 of the 3 columns listed (D, J, P). I do not know how to do this and am hoping someone can help. Please see attached spreadsheet.

    Thank you very much for your assistance in advance.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Count Unique Values with COUNTIFS?

    Im sure there is a better way to do this, but cant think of it right now - so give this a shot...

    1. remove the trailing space in B15
    2. Add a helper column with this (I used X)...
    =AND(COUNTIF($A$2:$A2,A2)=1,ISNUMBER(SEARCH("yes",D2&J2&P2,1)))

    Then for the count....
    C14=COUNTIFS($B$2:$B$11,B14,$X$2:$X$11,TRUE)
    copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-26-2011
    Location
    phoenix, arizona
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: How to Count Unique Values with COUNTIFS?

    Hello Ford, this works for me. I don't think I would have figured this out on my own... Thank you very much for your quick response and expertise!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Count Unique Values with COUNTIFS?

    Im happy it worked for you, thanks for the feedback

  5. #5
    Registered User
    Join Date
    07-26-2011
    Location
    phoenix, arizona
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: How to Count Unique Values with COUNTIFS?

    Hello Ford, I changed around some of the "Yes" values and it looks like the formula gives inconsistent results. Can you please help?

    For example, if row 2 has "No" in columns D, J, P, but row 3 has "No" in columns D, J and "Yes" in column P, the helper column renders "FALSE", when it should be "TRUE".

    I think the formula only works for the first row with that name in column A, and it does not work for subsequent rows that have the same name.

    Can you take a look at this again please? Thank you very much in advance.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Count Unique Values with COUNTIFS?

    hmm I see what you mean - I missed that

    Will the names always be grouped together like that? (please say yes lol)

  7. #7
    Registered User
    Join Date
    07-26-2011
    Location
    phoenix, arizona
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: How to Count Unique Values with COUNTIFS?

    LOL. I REALLY wish I could say yes, but they will not be.

  8. #8
    Registered User
    Join Date
    07-26-2011
    Location
    phoenix, arizona
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: How to Count Unique Values with COUNTIFS?

    Hello Ford,

    I am checking back in to see if you have a solution to the previous formula? If not, I want to thank you very much for your attempt at a solution.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Count Unique Values with COUNTIFS?

    Sorry for not getting back to you, this thread kinda got lost in the crowd - so thanks for bumping it I *think* I have it this time, but please test. I ended up with a bit of an ugly formula.

    Replace the helper formula with this...
    =IF(OR(COUNTIFS($A$1:$A1,A2,$D$1:D1,"Yes")=1,COUNTIFS($A$1:$A1,A1,$J$1:J1,"Yes")=1,COUNTIFS($A$1:$A1,A1,$P$1:P1,"Yes")=1)=TRUE,FALSE,
    OR(COUNTIFS($A$1:$A2,A2,$D$1:D2,"Yes")=1,COUNTIFS($A$1:$A2,A2,$J$1:J2,"Yes")=1,COUNTIFS($A$1:$A2,A2,$P$1:P2,"Yes")=1))

+ 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. Countifs for Unique Values
    By lucas813 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2015, 01:45 PM
  2. COUNTIFS unique values
    By Dan27 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-14-2014, 12:13 AM
  3. countifs for unique values
    By rbenguerel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-31-2014, 07:57 AM
  4. Replies: 5
    Last Post: 05-04-2014, 10:56 AM
  5. COUNTIFS criteria to specify unique values
    By Boatryte in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2014, 04:05 PM
  6. COUNTIFS with Unique Values
    By lefteegunzz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-23-2013, 04:25 PM
  7. Excel 2007 : Using countifs to count unique values
    By AlexZoom in forum Excel General
    Replies: 2
    Last Post: 09-23-2010, 09:41 AM

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