# Count Unique ID numbers with a condition

1. ## 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

2. ## 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.``

3. ## 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:
`Please Login or Register  to view this content.`

4. ## 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. ## 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. ## 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. ## 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. ## 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 a video which demonstrates how to use Power Query code provided.

9. ## Re: Count Unique ID numbers with a condition

Originally Posted by CARACALLA
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. ## Re: Count Unique ID numbers with a condition

Originally Posted by alansidman
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 a video which demonstrates how to use Power Query code provided.
Thanks @Alan I will try this over the weekend.

11. ## Re: Count Unique ID numbers with a condition

For office 365 only enter

12. ## 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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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