I have a column called ‘Age’ I want to count everyone that is below or equals to 14 years old
I tried this but it’s not working CountIf([Family Database].Age > 18 )
Any thoughts? 
[Family Database].CountIf(CurrentValue.Age <= 14) should work
Oh, I see.
If I’m referencing something I’ve already called out then it needs to be pointed out with: CurrentValue
Awesome. Thanks.
CurrentValue is to reference the item when iterating in e.g. Filter and FormulaMap. I’m not using CountIf so often, but I believe the mechanism is the same there. Did this work?
1 Like
Hello Juan! According this article
it is not recommended to use count if in your formulas, because it’ll be slower.
Instead, create a filter with your condition and then count the number of items that match said condition. In your case, your formula would end up looking like this:
[Family Database].Filter(Age<=14).Count()
1 Like
I have a feeling that for this use case (get people once, most likely in a canvas formula and not in a table) the optimization tip is not that relevant. I think the optimization in Filter(... = thisRow....) has something to do with repeated invocation with thisRow being different all the time (e.g. some sort of result indexing and reuse), otherwise I have no clue how it could possibly perform better. Common sense tells me that the single-shot invocation of CountIf should be faster than Filter().Count() because it doesn’t need to build a list — however if the latter doesn’t do that either then they should be equally fast.
You are totally right!
I was just pointing it out so he and other new users know there’s a faster way to do it when they start using it in tables
1 Like
I’m gonna check it though. There’s one thing that could be happening: Filter() using some sort of internal index for comparison (e.g. a b-tree for all records, O(log N)) and CountIf doing full traversal (O(N)).
P.S. You know, you’re right! CountIf IS slower, even in the canvas:
It doesn’t seem to depend from dataset size though, the difference is constantly 1.5–2.5 times whether it’s for 500, 1000, or 2000 rows. It kinda depends on the size of the filtered output though: the larger the resulting set, the smaller the difference. So I guess Filter does in fact build a list of rows as an intermediate step. And CountIf is just doing some comparisons slowly.
1 Like
This is great context, guys!
Thanks so much.
Loving this community.
1 Like