Forum Discussion

Copper Contributor

Jun 28, 2024

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.

Formulas and Functions

anshul_marele
Jul 06, 2024
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/

OliverScheurich
Gold Contributor
Jun 28, 2024
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.

BYCOL & BYROW.xlsx19 KB
- anshul_marele
  Copper Contributor
  Jun 28, 2024
  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
  - SergeiBaklan
    MVP
    Jun 28, 2024
    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.

Share

Resources