I'm using COUNTIF to compare an array to another array to calculate frequency. The intention is to create a spill array that lists frequency of events. My formula works properly, but when I try to filter on another column, the formula returns #VALUE! errors.

Here is a simplified example:

Me and you are recording who wins games in a table, with the winner in column Winner and the game played in column Game. You want to see who won the most, so you create a spill formula to calculate frequency (tags 'me' and 'you' are manually added):

=LET(
    KeyColumn, Table1[Winner],
    Categories, SORT(UNIQUE(KeyColumn)),

    Frequency, COUNTIF(KeyColumn,"="&Categories),
    Frequency
)

I see that you won more, but I'm a nerd so sports don't count. I made a table with a column called Not Sports and filtered the list using this formula:

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn,Table1[Game],
    FilterList, Table2[Not Sports],
    Categories, SORT(UNIQUE(KeyColumn)),

    BoolFilterColumn, ISNUMBER(XMATCH(FilterColumn,FilterList)),
    FilteredKeyColumn, FILTER(KeyColumn,BoolFilterColumn),

    Frequency, COUNTIF(FilteredKeyColumn,"="&Categories),
    Frequency
)

I get an array the length of FilteredKeyColumn with #VALUE! errors.

Am I doing something wrong? How can I fix this?

Note: This is just a part of a formula I am making and I actually do need the frequency array as shown.

Edit: Data for reference

Table1

Table2

CodePudding user response：

You need to add to the countifs and transpose the list of Not sports then use mmult to make it a 1d array:

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn,Table1[Game],
    FilterList, Table2[Not Sports],
    Categories, SORT(UNIQUE(KeyColumn)),
    Frequency, MMULT(COUNTIFS(FilterColumn,TRANSPOSE(FilterList),KeyColumn,Categories),SEQUENCE(COUNTA(FilterList),,1,0)),
    Frequency
)

Also by adding CHOOSE we can put the list of winners in the same formula:

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn,Table1[Game],
    FilterList, Table2[Not Sports],
    Categories, SORT(UNIQUE(KeyColumn)),
    Frequency, MMULT(COUNTIFS(FilterColumn,TRANSPOSE(FilterList),KeyColumn,Categories),SEQUENCE(COUNTA(FilterList),,1,0)),
    CHOOSE({1,2},Categories,Frequency)
)

A note as to why the error, you are passing an array to COUNTIFS where it will only accept a range. That is why the error.

CodePudding user response：

Scott Craner's answer works well. In my actual usage, I need to extend this to multiple filter criteria. Here is a formula inspired by Scott:

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn1,Table1[Game],
    FilterList1, Table2[Not Sports],

    Categories, SORT(UNIQUE(KeyColumn)),
    Bool2DKey, TRANSPOSE(KeyColumn)=Categories,
    Mask1, TRANSPOSE(ISNUMBER(XMATCH(FilterColumn1,FilterList1))),
    Bool2DMasked, Bool2DKey*Mask1,
    OnesArray, SEQUENCE(ROWS(KeyColumn),1,1,0),

    Frequency, MMULT(Bool2DMasked,OnesArray),
    Frequency
)

It creates an array Bool2DKey that is Categories tall by the height of Table1, filled with booleans indicating a category match in that column (table row).

Bool2DKey is further filtered by a boolean mask of the filter, in this case Mask1. This can be extended to more filters.

Like in Scott's example, I created an array (called OnesArray) to use in MMULT to sum the rows of Bool2DKey. Using the boolean array and MMULT negates the need for COUNTIFS.

Here is an extended example where I added more filters:

The formula is:

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn1,Table1[Game],
    FilterColumn2,Table1[Rules],
    FilterColumn3,Table1[Color],
    FilterList1, Table2[Not Sports],
    FilterList2, Table3[Rules],
    FilterList3, Table4[Color],

    Categories, SORT(UNIQUE(KeyColumn)),
    Bool2DKey, TRANSPOSE(KeyColumn)=Categories,
    Mask1, TRANSPOSE(ISNUMBER(XMATCH(FilterColumn1,FilterList1))),
    Mask2, TRANSPOSE(ISNUMBER(XMATCH(FilterColumn2,FilterList2))),
    Mask3,TRANSPOSE(ISNUMBER(XMATCH(FilterColumn3,FilterList3))),
    Bool2DMasked, Bool2DKey*Mask1*Mask2*Mask3,
    OnesArray, SEQUENCE(ROWS(KeyColumn),1,1,0),

    Frequency, MMULT(Bool2DMasked,OnesArray),
    Frequency
)

If you want to sum a column (assuming a column of values called Table1[Count]), the formula is:

=LET(
    KeyColumn, Table1[Winner],
    SumColumn,Table1[Count],
    FilterColumn1,Table1[Game],
    FilterColumn2,Table1[Rules],
    FilterColumn3,Table1[Color],
    FilterList1, Table2[Not Sports],
    FilterList2, Table3[Rules],
    FilterList3, Table4[Color],

    Categories, SORT(UNIQUE(KeyColumn)),
    Bool2DKey, TRANSPOSE(KeyColumn)=Categories,
    Mask1, TRANSPOSE(ISNUMBER(XMATCH(FilterColumn1,FilterList1))),
    Mask2, TRANSPOSE(ISNUMBER(XMATCH(FilterColumn2,FilterList2))),
    Mask3,TRANSPOSE(ISNUMBER(XMATCH(FilterColumn3,FilterList3))),
    Bool2DMasked, Bool2DKey*Mask1*Mask2*Mask3,
    Value2DMasked, Bool2DMasked*TRANSPOSE(SumColumn),
    OnesArray, SEQUENCE(ROWS(KeyColumn),1,1,0),

    Sum, MMULT(Value2DMasked,OnesArray),
    Sum
)