SOLVED

BYCOL & BYROW

Copper Contributor

BYCOL & BYROW

Hi,

Below formula throwing #Calc error:

BYCOL(C3#,LAMBDA(ColHdrs_1,
BYROW(B4#,LAMBDA(RowHdrs_1,
IFERROR(COUNTA(UNIQUE(FILTER(\$K\$3:\$K\$25,(\$I\$3:\$I\$25=RowHdrs_1)*(\$J\$3:\$J\$25=ColHdrs_1)))),0)))))

Need one array formula.

Sheet

8 Replies

Re: BYCOL & BYROW

=MAKEARRAY(5,4,LAMBDA(r,c,COUNTA(UNIQUE(FILTER(K3:K25,(I3:I25=INDEX(B4:B8,r))*(J3:J25=INDEX(C3:F3,c)))))))

Try the MAKEARRAY formula which returns the intended result in my file.

Re: BYCOL & BYROW

Very thanks @OliverScheurich

But why to complicate a simple logic, if it is possible in Google Sheets then would also possible in Excel.

Re: BYCOL & BYROW

So far Excel doesn't support nested arrays natively. Google Sheets supports.

As a comment, you use formula like IFERROR( COUNTA( UNIQUE(...

If UNIQUE returns an error, COUNTA gives 1. It counts all values, includes errors. Thus IFERROR never works here.

As variant that could be IFERROR( ROWS( UNIQUE(... which with gives 0 in case of error.

best response confirmed by anshul_marele (Copper Contributor)
Solution

Re: BYCOL & BYROW

=MAP(C3#&B4#,
LAMBDA(A,
COUNTA(UNIQUE(FILTER(K3:K25,(J3:J25&I3:I25=A))))))

This is the perfect solution by

Re: BYCOL & BYROW

Again, COUNTA gives wrong result. From my point of view more correct will be

``````=MAP(
C3# & B4#,
LAMBDA(A,
IFERROR(
ROWS(UNIQUE(FILTER(K3:K25, (J3:J25 & I3:I25 = A)))),
0
)
)
)``````

Re: BYCOL & BYROW

@anshul_marele I agree with @SergeiBaklan... COUNTA is incapable of returning 0 when used with the FILTER function. If FILTER does not find a match, it returns #CALC! because empty arrays are not supported. COUNTA only excludes empty (blank) cells in a range, so error values are counted as 1. Even if you tried using an empty string ("") for the [if_empty] argument, it would still be counted as 1.

In your sample file, COUNTA is returning 1 for "Best2", "Best3" and "Best4" when they should all be 0. If you filter the Lookup1 column by "Best", you will see the only value present in the Lookup2 column is 1.

An alternative approach would be to use the UNIQUE function on the lookup range first, then MAP can be used with a simple SUM or SUMPRODUCT equivalent of COUNTIF:

``````=LET(
unq, UNIQUE(I3:K25),
arr, CHOOSECOLS(unq, 1) & "|" & CHOOSECOLS(unq, 2),
MAP(B4# & "|" & C3#, LAMBDA(val, SUM(--(val = arr))))
)``````

Re: BYCOL & BYROW

If slightly modify @djclements idea, we may avoid COUNTA at all.

``````=LET(
data,   UNIQUE(\$I\$3:\$K\$25),
first,  CHOOSECOLS( data, 1),
second, CHOOSECOLS( data, 2),
left,   UNIQUE( first ),
up,     TOROW( SORT( UNIQUE( second ) ) ),
VSTACK(
HSTACK("", up),
HSTACK( left,
MMULT(--(TOROW(first)=left ),--(second=up  ))
)
)
)``````

Re: BYCOL & BYROW

@djclements , @SergeiBaklan Thanks :folded_hands:

1 best response

Accepted Solutions
best response confirmed by anshul_marele (Copper Contributor)
Solution

Re: BYCOL & BYROW

=MAP(C3#&B4#,
LAMBDA(A,
COUNTA(UNIQUE(FILTER(K3:K25,(J3:J25&I3:I25=A))))))

This is the perfect solution by