How do I count occurrences in an array column?

I’ve got a user ID and and item ID joined into a template column that is then fed into a list column and set as the buy button’s product ID/SKU.

I’m allowing clients to add multiple items to their cart prior to checking out and need to count how often an item ID occurs in the array column that is formed from splitting the list column.

Is there a way to count occurrences?

within an array column yet?

Have you tried a rollup?

Or are you trying to get something like 2,1,1 out of the array?

I’m trying to get just “2” from that first value that is seen twice.

@Jonathon_Kohn Well, I dont know your cenario, but I tried to replicate it.

You have this first relation I called rel/ items. Having this, you could try:

  • Relation from IDs to the unique IDs in the other sheet;
  • Rollup to count the unique IDs from this 2nd relation;

The 2 steps below I’m not sure if you need it, but I made it anyway:

  • If-Then-Else to hide zeros - when rollup equals 0 then blank, else rollup;
  • Join list from If-Then-Else to get the occurrences separeted by commas;

1 Like

See above screenshot. I want to count the number of times an ID occurs within an array column. The array column here is 2 comma separated lists combined into 1 column then split into an array. I want to count the times each ID occurs within that single column.

I think I have a Yes Code column almost ready to sent your way. Just to confirm, do you have this one in a comma-delimited format instead of an array?

1 Like

I believe what you need is this, right?

1 Like

Exactly… is that not currently possible without the code column?

Comma-delimited version:

Array version:

Enjoy :wink:


Ah I can’t immediately think of a solution without using the XC column but the links are there in the reply above, just plug it in and run!

1 Like

Love this phrase! :facepunch:t3::boom:

1 Like

One of the best things about XC/YC column eh :wink:

1 Like