Counting occurrences of dynamic (spilled) array elements in a table

Question

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

Answer 1

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))))

---