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.

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.

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

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.

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.