How do I count occurrences in an array column?

Ask for Help
1

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?

Screen Shot 2021-08-16 at 12.36.03 PM
Screen Shot 2021-08-16 at 12.36.03 PM704×200 56.2 KB

within an array column yet?

2

Have you tried a rollup?

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

3

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

5

@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;

image
image774×544 49.7 KB

2 Likes
6

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.

7

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
8

image
image1346×1150 67.1 KB

I believe what you need is this, right?

1 Like
9

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

10

Comma-delimited version:

Array version:

Enjoy :wink:

2 Likes
11

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
12

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

1 Like
13

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

1 Like
14

With all the new updates, is there a way to find occurrences in array without XC column? I have been trying all the array formulas in new column type, but unable to come up with solution.

Appreciate feedback.

15

yeah…

  • Use the Remove Element from Array plugin to remove all instances of the item you want to count
  • You now have two arrays; the original, and the smaller one with the item to be counted removed
  • Do a rollup on each to get a count of items
  • The difference between the two counts is the number of your items in the original array
3 Likes