+ Reply to Thread
Results 1 to 12 of 12

Count Unique ID numbers with a condition

  1. #1
    Registered User
    Join Date
    02-18-2022
    Location
    Sydney, New South Wales
    MS-Off Ver
    2019 Professional Plus
    Posts
    26

    Count Unique ID numbers with a condition

    Hi All

    I am encountering diffculties countingunique ID's with a single condition.

    In the example I have a data tab which itemises each transaction and in the summary tab I am looking to summarise groups and a value (not sub groups)

    The dififculty is column C of the summary tab.

    Does anyone have a formula I could use to count unique group numbers on a specific day?

    Regards

    Greg
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    887

    Re: Count Unique ID numbers with a condition

    CountIF, and honestly discard that use plural CountIFS. Why? Because the Count IF is a single condition formula where the plural IFS or CountIFS will allow multiple criteria to be utilized or just a single if needed... really no need to use the singular version... Thing to note is that the format or structure of the formula is almost reversed in the CountIF vs CountIFS

    for you - you would want to say ...

    Please Login or Register  to view this content.
    You can see I am reusing your week variable that you make on the first tab to align with the weeknum in your summary tab.

    Also your value field can be updated to be EXACTLY like that countifs, just adding the SUMRANGE of F on the Data tab

    Please Login or Register  to view this content.
    -If you think you are done, Start over - ELeGault

  3. #3
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    Microsoft Office 365
    Posts
    1,067

    Re: Count Unique ID numbers with a condition

    and if you had MS office 365 it would be a lot simpler with the following:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  4. #4
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    887

    Re: Count Unique ID numbers with a condition

    Hah - I almost offered that first then saw it was not a 365 user. I do love Unique, Filter, & Choose combos

  5. #5
    Registered User
    Join Date
    02-18-2022
    Location
    Sydney, New South Wales
    MS-Off Ver
    2019 Professional Plus
    Posts
    26

    Re: Count Unique ID numbers with a condition

    Thank you @EleGault for your feedback around IF v IFS) just to clarify although I am happy to sum all values according to specific date in this example, the purpose of the week number will be for charts and identifying of trends. So the formula I am looking for is to count unique values in the specified period. For example although there is 60 entries there is only 47 unique groups.

    Please let me know if I am not making sense

  6. #6
    Registered User
    Join Date
    02-18-2022
    Location
    Sydney, New South Wales
    MS-Off Ver
    2019 Professional Plus
    Posts
    26

    Re: Count Unique ID numbers with a condition

    Hi @dosydos & @EleGault I tried this unique, filter combo on work computer which has Micorsoft 365 apps and I couldnt get it to work. I had previously tried it as I found it online. Is there perhaps something I am doing wrong as I get zero next to each date.

  7. #7
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,309

    Re: Count Unique ID numbers with a condition

    Summary

    C2=IF(Summary!$A2<>"",SUM(IF(FREQUENCY(IF(Data!$A$2:$A$1000=Summary!A2,Data!$D$2:$D$1000),Data!$D$2:$D$1000),1)),"")

    copy down

    Control shift+enter

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,469

    Re: Count Unique ID numbers with a condition

    Here is an alternative solution using Power Query

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  9. #9
    Registered User
    Join Date
    02-18-2022
    Location
    Sydney, New South Wales
    MS-Off Ver
    2019 Professional Plus
    Posts
    26

    Re: Count Unique ID numbers with a condition

    Quote Originally Posted by CARACALLA View Post
    Summary

    C2=IF(Summary!$A2<>"",SUM(IF(FREQUENCY(IF(Data!$A$2:$A$1000=Summary!A2,Data!$D$2:$D$1000),Data!$D$2:$D$1000),1)),"")

    copy down

    Control shift+enter

    Thank you @Caracalla, This worked perfect for home computer however not on work.

    Should this also work with Microsfot 365 Apps for enterprise?

  10. #10
    Registered User
    Join Date
    02-18-2022
    Location
    Sydney, New South Wales
    MS-Off Ver
    2019 Professional Plus
    Posts
    26

    Re: Count Unique ID numbers with a condition

    Quote Originally Posted by alansidman View Post
    Here is an alternative solution using Power Query

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Thanks @Alan I will try this over the weekend.

  11. #11
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,309

    Re: Count Unique ID numbers with a condition

    For office 365 only enter

  12. #12
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    887

    Re: Count Unique ID numbers with a condition

    Hmm interesting that I glazed right past the post ...

    I went off the summary that you had setup for the sample... in the future when you request can you be sure to include a Results tab in which you have shown what it should be if you had working formulas... even if it is just a few lines explaining you want it in X Y Z format and doing A B C calculations...


    Now that I see what you are asking... Lets add "Helper Columns" to your data set and make this super simple. Also backwards compatible since I am not sure what is going on with your versions...

    I added 3 fields... you only need one depending on what grouping you are after...

    You will notice the only changes are the references to the time period... you have Date (Daily) in A, Month in B and Week in C.
    What I am going to do is add a column that counts how many times the time period with the specified group has appeared since the top of the data set.
    IF it is the first occurrence "=1" then we will record a 1, otherwise we don't want it so it is now a 0


    Helper Columns for Data Tab

    UniqueGroup_Daily - Data Tab - Column G, Enter first on G2
    Please Login or Register  to view this content.
    UniqueGroup_Weekly - Data Tab - Column G, Enter first on H2
    Please Login or Register  to view this content.
    UniqueGroup_Monthly - Data Tab - Column G, Enter first on I2
    Please Login or Register  to view this content.


    Then on the Summary Tab I am going to reference the DATE for Daily because your summary table is setup to show every day... if you want to do weeks or months you need to setup your summary table to be condensed otherwise we will be counting the same week/month over and over and giving you outrageous totals.

    Unique - Summary Tab - Column C, Enter first on C2
    Please Login or Register  to view this content.
    I think this is now to what you were shooting for...but let me know if I am still miss interpreting the ask.

+ 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] Unique count and output total count for IDs that meet condition
    By makaveeti in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-05-2022, 09:53 AM
  2. two condition before count unique text value.
    By Ensaac in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-28-2019, 09:00 PM
  3. [SOLVED] Count for unique value with condition
    By rchure in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-12-2018, 08:15 AM
  4. Need help - Count unique value that matches given condition
    By Mintha in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-02-2016, 09:19 AM
  5. How to Get Unique count with Multiple Condition.
    By jhabijay_10 in forum Excel General
    Replies: 1
    Last Post: 01-02-2015, 08:55 AM
  6. Count unique numbers with an AND condition????
    By ajajmannen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-05-2007, 09:15 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