+ Reply to Thread
Results 1 to 15 of 15

Is it possible to use Countifs to count only unique values based on a defferent data range

  1. #1
    Registered User
    Join Date
    03-16-2017
    Location
    Mars
    MS-Off Ver
    2016
    Posts
    11

    Question Is it possible to use Countifs to count only unique values based on a defferent data range

    I apologize in advance if I am not posting this in the correct place, or if this is a duplicate, I have searched is much as possible but with no luck...

    I trying to create dynamic report out of a work sheet I have and I am kind of stuck not able to get unique values.

    In A1:A15000k I have Ticket/Task#, the ticket can be worked on more than once, i am trying to apply another criteria to my formula to only allow unique Ticket numbers to be counted.

    here is the formula i am using now:

    Please Login or Register  to view this content.
    Non dynamic version of it

    Please Login or Register  to view this content.
    where Names represent a set of users....

    Sample data:
    Excel.jpg

    Thanks for all your help...
    Last edited by OmarQA; 03-16-2017 at 10:02 AM.

  2. #2
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    Do you want ticket numbers worked on more than once to only be counted once or not be counted at all?

    So, only unique numbers or the first iteration of each number?

    Counting duplicates as 1 would be

    =SUMPRODUCT(($A$1:$A$100<>"")/COUNTIF($A$1:$A$100,$A$1:$A$100&""))

    Change A to the column you are counting
    Last edited by DannyJ; 03-16-2017 at 10:10 AM.

  3. #3
    Registered User
    Join Date
    03-16-2017
    Location
    Mars
    MS-Off Ver
    2016
    Posts
    11

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    Duplicate ticket numbers should only be tracked once... i was going to try sum with frequency but i did not now how to fit that with the sumproduct that i am using for the namespace for one of the countifs...

    Thanks for taking the time to look at this....

  4. #4
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    No worries. I have put the formula you need above. Didn't see that you had already replied.

    You can easily change that to whichever dynamic you want to use.

  5. #5
    Registered User
    Join Date
    03-16-2017
    Location
    Mars
    MS-Off Ver
    2016
    Posts
    11

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    Thanks @DannyJ, for some reason this did not work, its giving me an error....

    Excel2.jpg

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

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    For best results attach a SMALL sample file (about 20 rows worth of data) and tell us what result you expect.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    03-16-2017
    Location
    Mars
    MS-Off Ver
    2016
    Posts
    11

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    Thanks @Tony Valko for your help, attaching sample file with this comment... The expected is to give me the count for unique ticket#, in the formula on the sheet that would be 27... and i am getting 33... hope this helps...

    Sample.xlsx

    Again thank you to all who have took time to look at this...
    Last edited by OmarQA; 03-16-2017 at 10:38 AM.

  8. #8
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    Why is it 27? It is 31? There are 31 different unique numbers.

    You have 3 extra JJ-585 abd 1 extra CA200. In total you ahve 35 records. 35 - 4 = 31.

    What error is it giving?
    Last edited by DannyJ; 03-16-2017 at 11:02 AM.

  9. #9
    Registered User
    Join Date
    03-16-2017
    Location
    Mars
    MS-Off Ver
    2016
    Posts
    11

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    Two unique numbers do not match an other criteria on Column G, only counting Major issues not critical...

    Just #N\A
    Last edited by OmarQA; 03-16-2017 at 11:04 AM.

  10. #10
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    Is the error shown on the formula I gave or have you edited it? Are there any blanks on unique number column?

  11. #11
    Registered User
    Join Date
    03-16-2017
    Location
    Mars
    MS-Off Ver
    2016
    Posts
    11

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    No blanks, I added your formula to what i l arleady have.

    there is a sample file attached above, if you would like to try to add your formula to the one in the sample...

  12. #12
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    I added it to that. It counts as 31. The formula was just to count the amount of unique, not the amount of unique with other criteria. Can you put helper columns in the sheet?

  13. #13
    Registered User
    Join Date
    03-16-2017
    Location
    Mars
    MS-Off Ver
    2016
    Posts
    11

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    Yes I can, keep in mind that I have multy criteria countifs with space name that have values.

    Thanks, for your help...

  14. #14
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    This should so what you want. I don't know the criteria so that's not right but once you correct that it will count as intended.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    03-16-2017
    Location
    Mars
    MS-Off Ver
    2016
    Posts
    11

    Re: Is it possible to use Countifs to count only unique values based on a defferent data r

    @dannyJ Thanks for your help, the helper column did the trick, don't know how i did not think of it earlier.

    Again thanks a lot for taking the time to help me with this...

+ 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] How to Count Unique Values with COUNTIFS?
    By Kingswood in forum Excel General
    Replies: 8
    Last Post: 08-01-2015, 08:02 PM
  2. Count unique values based off of data in 3 other columns
    By slatten in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-11-2015, 04:56 PM
  3. [SOLVED] Count unique text values within a range based on another column
    By Sebastes in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-14-2014, 01:38 PM
  4. [SOLVED] Count unique values in a data range with exemption
    By lheyanne in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 03-01-2013, 11:48 AM
  5. 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
  6. Excel 2007 : Using countifs to count unique values
    By AlexZoom in forum Excel General
    Replies: 2
    Last Post: 09-23-2010, 09:41 AM
  7. Replies: 6
    Last Post: 03-31-2009, 11:17 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