How to do a countif in a glide table, but with the first data row fixed and the rest of the rows keep being counted as the formula grows. I need a solution for Glide Tables only (not google sheets) Thanks in Advance.
See Example Image
mmm, this is a tricky one. I think the biggest challenge with this is that the usual approach of relation plus rollup wonāt work. Thatās because for each row, you only want to count the occurrences in the current row and all previous rows, right?
Anyway, I have a solution for you. But be warned, it isnāt simple. It involves just about every column type you can think of, and a bit of Javascript thrown in for good measure 
I donāt think I could explain it in words, so here is a video:
Here is the Javascript that does the counts from the joined list:
var arr = p1.split(',').map(Number);
const count = (arr, val) => arr.reduce((a, v) => (v === val ? a + 1 : a), 0);
return count(arr, p2) || undefined;
Hopefully somebody else will come up with a simpler solution.
2 Likes
Wow, thanks so much, iāve been wanting this for years, bummed i didnāt ask before. Okay iāll try this out. I will likely have to ask questions when it gets to the javascript. Thx 
Welcome.
To be honest, itās an ugly solution and Iām a bit annoyed that I couldnāt come up with something simpler.
Just thinking about it, if weāre going to resort to Javascript then we may as well do most of the work with JS and get rid of all those column gymnastics in the middle.
Give me a little bit to figure that out, and Iāll post an update in a while.
@ToOFa_Apps here, try this instead. Just 5 columns neededā¦
- Use the first 3 columns to create the incrementing row index as I described in the video
- Then a Joined List of all the values
- And then the Javascript column, which takes 3 parameters:
ā p1: the joined list column
ā p2: the Value column
ā p3: the Row Index column
The Javascript youāll need:
var arr = p1.split(',').map(Number).slice(0, p3+1);
if (p2 === 0) { return arr[0] }
const count = (arr, val) => arr.reduce((a, v) => (v === val ? a + 1 : a), 0);
return count(arr, p2) || undefined;
This is much cleaner 
3 Likes
Yeah definitely less columns and cleaner. I suppose if weāre using JS, might as well use it in the middle columns too. This is great! Iāll definitely have to chat more when it comes to the Javascript. Thanks a millionš
This was the issue I was having when trying to figure out streaks. Ended up figuring out a way to do this nocode, but it required quite a few columns to make it happen.
1 Like
Actually with this, you can relate the value column back to itself (multiple). Then do a lookup of the of relation to pull in the Row IDs of the relation and finally a find element index of the lookup columns to find the RowID.
3 Likes
Believe it or not, I watched that all the way through 
Iām still trying to figure out in my head why you needed all those extra columns to get the current streak. At 32:30 in the video, the first thing that occurred to me was ājust use a single value through the relation from the user profile and take the last valueā. Iām sure thereās a good reason why that doesnāt workā¦
oh, and you win the prize for the longest ever column names 
1 Like
Because the last value/record added might not be the last chronologically
1 Like
Yaā¦it was a long videoābut it took me 2 days of literal staring at the data editor to figure it out.
yeah, some crazy logic going on there. I have to admit that I got lost more than once trying to follow alongā¦
Building on the fundamental building blocks laid out by @Darren_Murphy, I think I come out with an alternative. I wanted to attempt this earlier but the plugin columns were mysteriously missing from my data editor for days. Just got them back hours ago thanks to Glide support team.
3 Likes
Nice
Thanks
Wow @chee. It took longer to figure yours out, there is some crazy stuff going on in there, but i love that its a no code solution. Well done and thanks a million. Will test that all out later today.
Iāve been using @Darren_Murphy solution till now and it also works amazingly well. I tried so many solutions in my early days of glide (March 2020) and kind of gave up. But this simple āDrag Countā method (as iāve been calling it) is really crucial in an app i havenāt been able to create till now (without using a google sheet). Itās so much quicker on a table compared to sheets.
Really good to know we have the tools and the experts available to help with these complex calculations. Thanks guys!!!
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.