# Countif in Glide Table

**URL:** <https://community.glideapps.com/t/countif-in-glide-table/40369>\
**Category:** Ask for Help\
**Created:** [April 3, 2022, 12:52pm UTC](https://community.glideapps.com/t/countif-in-glide-table/40369 "2022-04-03T12:52:28Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![ToOFa\_Apps](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/toofa_apps/32/36816_2.png) [@ToOFa\_Apps](https://community.glideapps.com/u/ToOFa_Apps)\
**Post date:** [April 3, 2022, 12:52pm UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/1 "2022-04-03T12:52:28Z")

</div>

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

 ![Screenshot 2022-04-03 at 14.49.51](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/5/35d27711ac98fd1f9535d0ddfdaa4e2ea62baa8e.png)

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 3, 2022, 1:55pm UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/2 "2022-04-03T13:55:07Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![ToOFa\_Apps](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/toofa_apps/32/36816_2.png) [@ToOFa\_Apps](https://community.glideapps.com/u/ToOFa_Apps)\
**Post date:** [April 3, 2022, 2:36pm UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/3 "2022-04-03T14:36:15Z")

</div>

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 👍

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 3, 2022, 2:51pm UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/4 "2022-04-03T14:51:18Z")

</div>

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…

 ![Screen Shot 2022-04-03 at 11.02.47 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/7/572f6c81f852f9917a16778132841c3d3a32cb44.png)

- 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:

```auto
  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 🙂

---

<div class="post-metadata">

**Author:** ![ToOFa\_Apps](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/toofa_apps/32/36816_2.png) [@ToOFa\_Apps](https://community.glideapps.com/u/ToOFa_Apps)\
**Post date:** [April 3, 2022, 4:06pm UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/5 "2022-04-03T16:06:39Z")

</div>

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👍

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [April 3, 2022, 7:19pm UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/6 "2022-04-03T19:19:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [April 3, 2022, 7:22pm UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/7 "2022-04-03T19:22:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 4, 2022, 12:39am UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/8 "2022-04-04T00:39:33Z")

</div>

> [@Robert\_Petitto](#):
>
> This was the issue I was having when trying to figure out streaks.

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 🤣

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [April 4, 2022, 1:16am UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/9 "2022-04-04T01:16:43Z")

</div>

> [@Darren\_Murphy](#):
>
> “just use a single value through the relation from the user profile and take the last value”

Because the last value/record added might not be the last _chronologically_

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [April 4, 2022, 1:19am UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/10 "2022-04-04T01:19:28Z")

</div>

Ya…it was a long video—but it took me 2 days of literal staring at the data editor to figure it out.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 4, 2022, 1:20am UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/11 "2022-04-04T01:20:42Z")

</div>

yeah, some crazy logic going on there. I have to admit that I got lost more than once trying to follow along…

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [April 4, 2022, 1:33am UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/12 "2022-04-04T01:33:21Z")

</div>

> [@Darren\_Murphy](#):
>
> I got lost more than once

😕

---

<div class="post-metadata">

**Author:** ![chee](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/chee/32/17040_2.png) [@chee](https://community.glideapps.com/u/chee)\
**Post date:** [April 7, 2022, 11:11am UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/13 "2022-04-07T11:11:07Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/a/5ad940a2a54191b331cae2a814ffc9deac9450e0.png)

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 8, 2022, 4:19am UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/14 "2022-04-08T04:19:10Z")

</div>

Nice 👍

---

<div class="post-metadata">

**Author:** ![elmerdubon](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/elmerdubon/32/78942_2.png) [@elmerdubon](https://community.glideapps.com/u/elmerdubon)\
**Post date:** [April 21, 2022, 8:15pm UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/15 "2022-04-21T20:15:35Z")

</div>

Thanks

---

<div class="post-metadata">

**Author:** ![ToOFa\_Apps](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/toofa_apps/32/36816_2.png) [@ToOFa\_Apps](https://community.glideapps.com/u/ToOFa_Apps)\
**Post date:** [April 25, 2022, 10:22am UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/16 "2022-04-25T10:22:18Z")

</div>

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!!!

---

<div class="post-metadata">

**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\
**Post date:** [April 26, 2022, 1:38pm UTC](https://community.glideapps.com/t/countif-in-glide-table/40369/17 "2022-04-26T13:38:57Z")

</div>

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.