# Hit a wall - count occourences of a value

**URL:** <https://community.glideapps.com/t/hit-a-wall-count-occourences-of-a-value/68709>\
**Category:** Ask for Help\
**Created:** [December 14, 2023, 4:43pm UTC](https://community.glideapps.com/t/hit-a-wall-count-occourences-of-a-value/68709 "2023-12-14T16:43:49Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Adam\_Chambers](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/adam_chambers/32/67402_2.png) [@Adam\_Chambers](https://community.glideapps.com/u/Adam_Chambers)\
**Post date:** [December 14, 2023, 4:43pm UTC](https://community.glideapps.com/t/hit-a-wall-count-occourences-of-a-value/68709/1 "2023-12-14T16:43:49Z")

</div>

Hi everyone,

I have a big sheet with multiple hotels on it

There is a column for ‘state’, with the state of each hotel in it.

How can I create a column that counts the number of occourences of each particular state.

I want to display a hotel’s position in the state for an ‘overall rating’ score I’ve created.

For example ‘1st of 55 in state’.

I’ve tried a couple of other tutorials but think they are for slightly different use cases.

Thanks!

---

<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:** [December 14, 2023, 4:51pm UTC](https://community.glideapps.com/t/hit-a-wall-count-occourences-of-a-value/68709/2 "2023-12-14T16:51:43Z")

</div>

Start with a Query column that targets your Hotels column, filter it by “State is This row-\>State”, and order it by your Rating column.

To get an overall count, add a Rollup-\>Count that targets the Query column.

To get each Hotels rank with the State, add a Lookup column that targets the Query column to get an array of the Rating scores, then use a Find Element Index column to find the Rating Score for each row within that array. This will be zero based, so use a math column to add one to the result.

---

<div class="post-metadata">

**Author:** ![Adam\_Chambers](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/adam_chambers/32/67402_2.png) [@Adam\_Chambers](https://community.glideapps.com/u/Adam_Chambers)\
**Post date:** [December 14, 2023, 5:04pm UTC](https://community.glideapps.com/t/hit-a-wall-count-occourences-of-a-value/68709/3 "2023-12-14T17:04:52Z")

</div>

Thanks Darrenn, it worked.

I have 1 more step.

Now it shows how many hotels there are in each state.

I need to calculate the first part of the sentence: \_\_\_\_\_\_\_\_\_\_\_\_\_ of 7 hotels in state.

I have a Look Up column that is currently ranking all rows.

Is there an adjustment so a lookup can be filtered to only show results for 1 state.

For example, instead of the lookup ranking every singe row, it would only rank rows where the state value is “Vermont”.

Thanks!

---

<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:** [December 14, 2023, 5:11pm UTC](https://community.glideapps.com/t/hit-a-wall-count-occourences-of-a-value/68709/4 "2023-12-14T17:11:40Z")

</div>

> [@Adam\_Chambers](#):
>
> Is there an adjustment so a lookup can be filtered to only show results for 1 state.

If you constructed the Query column as I suggested, you should already have that.