How to count number of unique values ONLY in the month/year they first appear

[LangePier]

Hey all,

For a month or two i've been discovering excel and I've been working on a big excel file to do all my business administration and im nearing the end, but there's a major hurdle i can't overcome. So in my 'hours' administration sheet i register how much time i spend on different jobs and different projects. The jobs and projects and clients all have a different column in the big time registry table. Above the table i want to create an overview of all the months with some interesting data about time spent on different projects, clients etc. And i am trying to create a column that lists the number of NEW projects started in that month.

Example: if i start a new project in february, and work on that project until april, i will first mention that project in my time registry table in february, but it will also appear in later registrations in march and april. Now i only want to count that project in february, not in march or april. With the help of ChatGPT i've tried a dozen or so different formulas already, with most of them failing at some point. I've left three different formulas in columns G, K and N - just out of interest to show where each of them go wrong. The formula in column G seems to be the best one for now, as it correctly only counts a recurring project in a single month and disregards it in another month. You can see this happening for the latest line of date in row 87, which has a project name that has already been listed before. The result is that the number count for months january (G2) and december (G13) gets confused, because it detracts 1 (counted project) from january and adds it to december. This should work exactly the other way around: the recurring project written in row 87 (and first in row 15) should be counted only for january and NOT for december, as january is the older of the two months and so it was a NEW project in january, and an old recurring project come december.

Another issue is that all formulas are blind for the years, which would mean that every year around january and december the number count would be a big mess ... I feel like a 'helper' column in column AC that could distill some data would probably make things a lot more fluid and accurate, but im really way in over my head and have no clue how to solve these issues. If anyone could shine a light on this complex issue i would be super grateful.

Thanks in advance,

Floris

Here's a bare bones example of my sheet and the formulas described (in the sheet 'time registration'):

unique project count per month2.xlsm

Ps. cell F1 holds the 'category' criterion that is applied to all the formulas. This one is absolutely neccessary because i have many different categories i want to be able to look for. I am self-employed and do all sorts of different work, hence the many options for further specification within the table

[AliGW]

Is this what you want?

=COUNT(UNIQUE(FILTER(Table_Times[Project],Table_Times[Mnd]=F2)))

Or this?

=COUNT(UNIQUE(FILTER(Table_Times[Project],(Table_Times[Mnd]=F2)*(Table_Times[Cat]=$F$1))))

[LangePier]

Hi AliGW, that is one hell of a quick response haha, thanks! Unfortunately both formulas don't give the right result, or at least not my desired result. I've added them to the overview in the top and marked the cells red which display the wrong result. In column Q i manually wrote the correct result as i would like to see them. So the problematic row is row 87 here, which lists project "1" for a second time, as the first time "1" is mentioned as a project is in row 15 at the top of the table. I want this project to only be counted in the oldest month, so january, and disregarded the second time it is listed, so in december. I hope this clears things up.

See here: [ATTACH]unique project count per month2.xlsm[/ATTACH]

edit: excuse me, i accidentally manually wrote 4 as the correct result for the count of december, but this should be 5, as each month has 5 unique project names to begin with. So when row 87 (or whatever later row) lists a project name that has already appeared in the table, it should just ignore it, since it should already have been counted before. I changed the attached file, so the example should be correct now. Also note how the formula does work correctly when project "1" is listed for a second time, but with a month equal to the first registry of "1" (january). I highlighted it yellow at the lower bottom of the table

[AliGW]

Right - gotcha! Expected results would have helped right fro the outset! I'll have another look.

[LangePier]

yep, expected results is a good way to clarify things, lesson learned haha. I just edited the attached file with a bit of further explanation in my previous comment (not sure if you get a notification of an 'edit' so here's to make sure)

edit: actually all the rows from 75 until 86 have second mentions of a project name, but all in the same month as the first mention of that specific project name. These listings are disregarded correctly by the formula. It really goes awry when a project name is mentioned again but in a different month than the original first mention.

[AliGW]

This does what I think you are describing, however I believe your expected result for December is one short:

=LET(u,UNIQUE(FILTER(Table_Times[Project],Table_Times[Cat]=$F$1)),h,HSTACK(u,BYROW(u,LAMBDA(r,INDEX(Table_Times[Mnd],MATCH(r,Table_Times[Project],0))))),COUNT(UNIQUE(DROP(FILTER(h,INDEX(h,,2)=F2),,-1))))

[LangePier]

Hmm after i replaced the , seperators for ; I got the general error "There's a problem with this formula. Not trying to type a formula? blablabla" and it highlights the "r" just before ";INDEX". Might this have to do with my version of excel perhaps?

This is my version:
excel version.png

I thought i had 365, since it says "microsoft 365 updates" but now im wondering if that is correct .. and if so, is this functionality reserved for a different version than mine perhaps? Or am i simply doing something wrong? this is the exact code when it prompts the error: =LET(u;UNIQUE(FILTER(Table_Times[Project];Table_Times[Cat]=$F$1));h;HSTACK(u;BYROW(u;LAMBDA(r;INDEX(Table_Times[Mnd];MATCH(r;Table_Times[Project];0)))));COUNT(UNIQUE(DROP(FILTER(h;INDEX(h;;2)=F2);;-1))))

[AliGW]

Damn! You aren't using 365 at all - you need to change your forum profile to Excel 2021. Several of the functions I used aren't available in your version.

Sorry - I'm out of time now. Someone else may be able to assist further.

[LangePier]

Uuughhhh i'm so so sorry

i considered getting 365 just now, just to get this formula working, but its really too expensive just for this, especially if needed for the rest of my professional career ...

Anyone else have a clue how to transform the formula into something that works for Excel 2021? Can it be done at all, it looks so incredibly complex ...

[AliGW]

It will be doable - but there are others better at pre-365 formulae than me. I'll put out a call for help if nobody assists soon (but please remember it's the weekend).

[windknife]

For Excel 2021, how about this in AE2 and copy down.

Please Login or Register  to view this content.

[LangePier]

Thanks, i'd appreciate that a lot.

I've made the example set of data a bit smaller, because i've got a good feel where exactly things go wrong now. The persisting problem is that second listings of project names in a different month than the original are not disregarded, but counted anyway (in different ways, depending on the formula - i've tried many and left the 'best' ones in the top overview). I've added some clear text and examples in the bottom two rows of the table, to play around with and see the problem clearly. I've been quite obsessed with the problem for the entire day now and officially throw in the towel ... if anyone could help me out i'd be super happy

Example sheet: unique project count per month2.xlsm

[AliGW]

Did you see post #11? Please feed back on that.

[LangePier]

Oops we must have crossed each other, i hadn't seen that post. And it works! Almost, that is :P. It still has a few minor flaws which i think should be relatively easy to fix (as this already feels like we climbed mount everest, all else should be easy :P).

Two remaining issues:
- the formula counts "" as a unique project name, which is understandable but i would very much prefer it not to (as leaving a cell empty would be the only way to purposefully circumvent a line of date being counted, which sometimes i will want to do)
- the formula results in "0" when a single different category number is within the category column (B). This is a major issue, as of course my real-life sheet will have many different categories

I've added some examples at the bottom of the table to demonstrate the problem:

unique project count per month3.xlsm

Thanks alread for bringing me one step further!

[AliGW]

Try this:

Please Login or Register  to view this content.

This should work if you are adding new rows correctly to your table.

[AliGW]

Actually, try this instead:

Please Login or Register  to view this content.

[LangePier]

wow it works! Even when i try to confuse the formula by changing the already-present data so that the first mention of a project suddenly shifts many months forward, the formula adapts along with it and shifts the count of that project to the proper month, that is marvelous!! With some help of ChatGPT (incredibly useful for simple changes and formulas :O) i managed to add a criterion for the year as well, as i just found out that every transition of the year - and shared projects across both december and january - will completely ruin the accuracy of the formula. So i'll just have to have a hard cut in the smoothness of the overview there, but that's quite fitting for the end of the year anyway, so perfectly fine :D.

Thanks so much! I think i've spent 6 hours or so trying to figure this out, and with a couple of formulas and changes you guys have managed to solve it completely, as good as i could have wished for. You guys are priceless, especially you AliGW, thanks so much!! Now let's enjoy our weekend haha

edit: i may have cheered a bit too soon ... there seem to be some inconsistencies, probably related to the year criterion, trying to figure it out

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[LangePier]

Right, indeed i didn't know that, i used the function on both of you for helping me so amazingly . J

I just finished figuring out what was going on that had me so confused; apparently the formula works neatly from the top to the bottom of the table, which means that if it encounters a registry with a new project name it will automatically assign that project to THAT date (month). This wouldn't be an issue in most cases, where everything is entered into the table in correct chronology, butttttt ... the first reason why i consulted this forum two months ago was to create a button that could move the entire table down and create an empty row at the top of the table, so that i would never have to scroll down to enter in a new line of data. The result is that the chronology of my table is in reverse (if not completely mixed up because im sorting the data for different columns). So when i just implemented the formula in my real sheet, i was shocked to find out the project counts didn't make any sense at all. But luckily when i sorted the date column to 'from old to new' then the formula adjusted correctly again and does display the correct number of projects for each month. Phewwww ... that almost gave me a heart attack. So i added a big button with a text as a reminder to myself to always first sort from 'old to new' and then i can read the overview safely.

So i think this topic and my issue have been completely and utterly solved!

Thanks so much both of you!

[windknife]

You are welcome.

[AliGW]

Phewwww ... that almost gave me a heart attack. So i added a big button with a text as a reminder to myself to always first sort from 'old to new' and then i can read the overview safely.

No need for that - the formula can do it before is starts calculating:

Please Login or Register  to view this content.

[LangePier]

Absolute perfection, love it!! With a bit of help from chat GPT I managed to successfully add the filter for the year as well:

=LET(
yearCriterion; $O$1;
z; SORT(Table_Times[[Datum]:[Mnd]]; 1; 1);
a; FILTER(INDEX(z;;5); (INDEX(z;;2)=$F$1)*(INDEX(z;;5)<>"")*(YEAR(INDEX(z;;1))=yearCriterion));
b; UNIQUE(a);
c; XLOOKUP(b; a; FILTER(INDEX(z;;27); (INDEX(z;;2)=$F$1)*(INDEX(z;;5)<>"")*(YEAR(INDEX(z;;1))=yearCriterion)); ""; 0; 1);
IFERROR(ROWS(FILTER(b; c=F2)); 0)
)

Thanks so much!!

[AliGW]

Well done - I forgot to add that in. But now you don't need to worry what order the data is in.

I'm a bit surprised you weren't able to work it out without ChatGPT, though - I would strongly suggest that you reverse engineer for full understanding and don't hesitate to ask here for an explanation of the workings if necessary.

[LangePier]

yeah it really takes away all potential for confusion, and makes the entire sheet perfectly clean and intuitive again - so happy with it :D

[AliGW]

Just shout if you need a full explanation of workings.

[AliGW]

Cherry on the cake - here's a spill array version that does NOT need to be copied down:

Please Login or Register  to view this content.

See cell O2.

[AliGW]

If you get a #SPILL! error, it will be because you have not emptied the cells that the resulting array needs to spill into. So, for the formula in O2 to work, O3:O5 must be empty first. Then paste the formula into O2.

[AliGW]

Any comment? Is this of any use to you?

[LangePier]

Wow, however impressive that is, i will prefer the copy-down version for now, 'cause the future-me will be utterly confused when wanting to change anything about this formula; best to keep it as 'simple' as possible for now hehe. My next focus is to make all the formulas for calculating average time spent per project, average income per project and search functions to do so for specific clients or specific projects > this is more in my comfort zone when it comes to excel haha. I would definitely want to understand the let and index and xlookup functions, but for now i think i better focus on the task at hand. Every challenge i encounter though teaches me something new again, even these complex things provide some more insight in how things work, although i definitely cant begin to formulate such a complex formula myself. I'm sure i'll encounter a next hurdle soon enough though, so perhaps that will be the opportunity to really get my hands on those things. Thanks a lot!

[AliGW]

Fine - well, it's there when you are ready to progress your learning.

[LangePier]

Yep! I already added a link to this topic to my sheet, in case i need to look things up or want to work with the formula and/or get a better understanding of it