arraysexcelvbadynamicuser-defined-functions# Counting occurrences of dynamic (spilled) array elements in a table

I'm using the new dynamic result functions like unique, filter, sort, A6#, etc. I like that they right size to the number of items they return.

I have a large data set in an Excel table (hundreds of columns and thousands of rows). I've extracted the unique values of one column that meet a criterion into a dynamic array using the unique/filter functions without issue.

Now I want to count the occurrences of each of the returned values in a dynamic way so that the range of the returned values scales with the number of unique values that are dynamically returned by the unique function. I can use the count function around a filter function to count the values one at a time, but the result is not dynamic, requiring me to manually copy/delete the function into the correct number of cells.

In summary, I'm looking for a way to count items in a table column that meet a criterion and are equal to all the values in a dynamic array. The result dimensions should agree with the dimensions of the dynamic array.

I started with some sample data in a table:

Color | Type | Cost | Criteria |
---|---|---|---|

Red | 1 | 1 | TRUE |

Blue | 2 | 2 | FALSE |

Red | 1 | 3 | TRUE |

Yellow | 1 | 2 | TRUE |

Orange | 1 | 3 | FALSE |

Red | 2 | 1 | TRUE |

Orange | 2 | 2 | FALSE |

Yellow | 1 | 3 | TRUE |

Orange | 2 | 2 | FALSE |

Blue | 1 | 1 | TRUE |

Red | 1 | 3 | TRUE |

Blue | 2 | 2 | FALSE |

Yellow | 2 | 1 | TRUE |

Red | 2 | 3 | FALSE |

Red | 2 | 1 | FALSE |

(Color Type Cost Criteria Red 1 1 TRUE Blue 2 2 FALSE Red 1 3 TRUE Yellow 1 2 TRUE Orange 1 3 FALSE Red 2 1 TRUE Orange 2 2 FALSE Yellow 1 3 TRUE Orange 2 2 FALSE Blue 1 1 TRUE Red 1 3 TRUE Blue 2 2 FALSE Yellow 2 1 TRUE Red 2 3 FALSE Red 2 1 FALSE)

The desired output is a range with two columns, Unique colors where the criteria is true, and number of occurrences where that color appears, and the criteria is true.

Generating the first column was simple:

```
=UNIQUE(FILTER(Table1[Color],Table1[Criteria]))
```

In this example the function returns a three-row single column: Red Yellow Blue It is simple to count the number of occurrences of a single member of that range with a formula that returns a single cell:

```
=COUNT(FILTER(Table1[Cost],Table1[Criteria]*(Table1[Color]=H10)))
```

Where I used Cost as it is numeric and works better with Count, and H10 is one of the cells in the dynamic array above 'Red' for example.

This function accurately counts what I want, but it does not shrink and expand as the number of colors returned to the table above changes, so I must go in and delete/copy the formula to the appropriate range. As my users will change the criteria column constantly, this leaves the two columns of different lengths often.

Next I converted this formula to the LET version which gave the same result.

```
=LET(lookup,H10,in,Table1[Color],cnt,Table1[Cost],crit,Table1[Criteria],
COUNT(FILTER(cnt,crit*(lookup=in))))
```

I then replaced the reference to the color in the formula (H10) with the dynamic array containing it (H10#), the result was a single cell with a value of 0.

I then deleted the count function leaving just the filter function and it returned NA. So, the filter function did not handle the H10# reference and returned a single value, NA.

I decided I needed to write a counting function that could handle the dynamic reference, I ended up with:

```
Public Function DynamicCount(SearchIn, LookFor As Variant As Variant
Dim arr As Variant
Dim idx As Long
ReDim arr(1 To LookFor.Rows.Count, 1 To 1)
For idx = LBound(arr, 1) To UBound(arr, 1)
arr(idx, 1) = Application.WorksheetFunction.CountIf(SearchIn, LookFor(idx, 1))
Next idx
DynamicCount = arr
End Function
```

I tested this first without the criteria, simply entering:

```
=DynamicCount(Table1[Color],H10#)
```

This worked returning the following spilling from a single cell:

6 3 3

I then tried applying the criteria using:

```
=DynamicCount(FILTER(Table1[Color],Table1[Criteria]),H10#)
```

This returned a value error! I'm not sure what is going on. I evaluated just the Filter part:

```
=FILTER(Table1[Color],Table1[Criteria])
```

This returned a spilled list of 8 colors as expected. After playing with the vba code a little, I think the problem is that the filter function is converting a range to a variant array. Not sure how to work with them. Did some msgbox debugging and when the SearchIn parameter is a Variant (as opposed to a range) It seems to crash on the following line.

`arr(idx, 1) = Application.WorksheetFunction.CountIf(SearchIn, LookFor(idx, 1))`

Any help would be appreciated, either with accessing variant arrays or other ways to tackle the problem. There must be an easier way... Thanks

Solution

Try using `MMULT()` -- No `VBA`

required as well without any `LAMBDA()` helper functions!!

• Formula used in cell `F2`

```
=LET(
α, FILTER(HSTACK(Table_1[Color],Table_1[Criteria]),Table_1[Criteria]),
Ψ, TAKE(α,,1),
φ, DROP(α,,1),
UNIQUE(HSTACK(Ψ,MMULT(N(Ψ=TOROW(Ψ)),φ))))
```

Or, a bit shorter way:

• Formula used in cell `F2`

```
=LET(
α, HSTACK(Table_1[Color],MMULT(N(Table_1[Color]=TOROW(Table_1[Color])),Table_1[Criteria])),
UNIQUE(FILTER(α, DROP(α,,1))))
```

- Function argument list doesn't match function template (function template is made for passing stack allocated array)
- Why is this Array.prototype.reduce method not working
- Find Repeat Sequence in an Array
- Removing duplicate values from a PowerShell array
- How to make arrays/json appear in the correct way
- Python: How to get values of an array at certain index positions?
- How to remove Object from array using mongoose
- How can I count the length of char in a char array without using any loop?
- How to transform nested JSON to CSV with Flatten?
- Fetch Max & Min number in a VBA collection
- ValueError: shape mismatch: objects cannot be broadcast to a single shape
- Group values from a 3d array into an associative 2d array and sum values with a shared path
- Group data from a 2d array by one column then conditionally populate keys within the group and count the number of subarray key occurrences
- How to extract matching strings from array and create new array
- Split flat array into groups/chunks of N elements then sum each group
- Dart List<dynamic> Can't Convert In To List<MyModel>
- C - Return a modified array of integers using pointers
- How to find number of occurrences based on input value
- How to flatten nested array struct in Bigquery sql?
- Using a string to define Numpy array slice
- javascript - match string against the array of regular expressions
- C Problem passing pointer to struct in an array
- Select a certain element from a .map array in react
- How to create object-based form-data when there are multiple, equally named form-elements?
- Numpy Array of Numpy Arrays. Reshaping not working as expected
- When I print the array in unorder list, it will duplicate print on click event
- Map function not returning all the arrays
- How to count Matching values in Array of Javascript
- Kotlin: Create and refer true Java arrays (for JNA)
- subsequent IF statement: shorter ways to assign values to parameters based on another variable value