Counting the number of occurrences based on multiple criteria

[Drew_Timmermans]

I can’t seem to figure out how to count the number of occurrences for a given set of criteria.

The original table is taken from a community doc on a habit tracker, but I want to track the number of times in a given week I complete a habit, as I don’t always create habits that I need to complete on a daily basis (some habits are only 2x/week). As you can see from the table, the way this document tracker tracks things is each day it adds the habits into ‘All Habits’ table, and then if I marked the task as complete on another table, it changes the values in the table. I added in the ‘Week Number’ column in order to try and count the number of times I completed each habit in a given week, but have been struggling to figure that out.

I’d love some assistance with understanding how to count the number of occurrences based on multiple criteria. My plan is to put the number of times I complete something each week in ‘Weekly Completed’, and would like for this to reset every Monday. I have the current week number set up such that the week number will automatically update for me, as seen above the first table. Thank you

[Pch]

Hi @Drew_Timmermans and Welcome to the Community !

In order to get some help from anyone here, could you check the sharing permissions of your embedded doc ?

Specifically: In Get Link, could you check if Anyone with the link is set as Can view ? (We can’t see you embed at the moment )

[Drew_Timmermans]

Thank you! Great to be in the community!

First time sharing, so I appreciate the help! I just changed the Get Link settings so now anyone with the link can view. Please let me know if that didn’t work!

[Pch]

No worries, it happens !

And I can confirm it is now working perfectly accessible !

[Agile_Dynamics]

sorry if i am a bit off-topic here, but @Pch , could you also post a quick explanation of how you fixed the issue, and what was incorrect in the original… then we can all learn from the exchange.

just looking at the ‘corrected’ document dont let me learn from this.

respect
max

[Pch]

I didn’t change anything in the doc @Agile_Dynamics . The doc was just not accessible/visible when I stumble on the post .

So I just asked to check on the sharing permissions but didn’t made any attempt to solve the issue so far !

[Agile_Dynamics]

ah… i misunderstood, apologies, i thought i was missing one of your pearls of wisdom

sorry
max

[Pch]

Hi again @Drew_Timmermans !

I think I have something for you …
At least, after copying your sample doc and few quick tests this might be the formula you were looking for …

[All Habits].Filter(
  Habit = thisRow.[Habit Name] 
  AND [Done Mark]="Done!" 
  AND [Week Number] = Current_Week
).Count()

What it does is :

It takes the whole table [All Habits] and checks

• If the CurrentValue.Habit is equal to thisRow.[Habit Name]
  Which, if the formula was stopping here, would return in your column all the rows from the [All Habits] table where CurrentValue.Habit = thisRow.[Habit Name]. This is the first step of the filter.

AND

• If the CurrentValue.Done Mark is equal to "Done!"
  Now in the list of rows from the “previous step” the filter checks the “status” of those habits. If it’s "Done!", it will keep them…and if not, ignore them.

AND

• If the CurrentValue.Week Number is equal to Current_Week.
  Last step, in the list of rows “returned” by the “previous steps”, the filter will look at the CurrentValue.Week Number and if it’s equal to the value in the canvas formula it will keeps them (once again, ignore them if not).
  Note that Current_Week here is the name I gave to your named formula on the canvas .

All this returns a filtered list of rows from the table [All Habits] which you just need to Count() .

Hope this helps
(Don’t hesitate if you need more explanations )

---

Side note: I poorly chose my words @Agile_Dynamics !

[Drew_Timmermans]

Thank you so much @Pch !! This definitely makes so much sense now that I see it. Much appreciated!