SOLVED

BYCOL & BYROW

Copper Contributor

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

@anshul_marele 

=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.

 

makearray.png

Very thanks @OliverScheurich 


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

Google Sheets Link

@anshul_marele 

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

@SergeiBaklan @OliverScheurich 

 

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

This is the perfect solution by 

Julian Poeltl

 

https://www.linkedin.com/in/julian-p%C3%B6ltl/

 

@anshul_marele 

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

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

@anshul_marele 

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

image.png

@djclements , @SergeiBaklan Thanks :folded_hands:

 

1 best response

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

@SergeiBaklan @OliverScheurich 

 

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

This is the perfect solution by 

Julian Poeltl

 

https://www.linkedin.com/in/julian-p%C3%B6ltl/

 

View solution in original post