Hit a wall - count occourences of a value

Ask for Help
1

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!

2

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.

1 Like
3

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!

4

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