Forum Discussion

anshul_marele's avatar
anshul_marele
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))))) ...
excel
Formulas and Functions
OliverScheurich's avatar
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.

 

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Jul 27, 2024

      anshul_marele 

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

      That is not the way it works.  Excel and Sheets are competitor products and each company is free to make its own decisions.  Personally, I agree with you in that Microsoft was guilty of a massive error of judgement when they decided, for backward compatibility reasons, that arrays of arrays were not supported.

       

      The array of array problem affects most of the formulas I write, frequently at more than one point within the code.  I have versions of the Lambda helper functions that provide workarounds, using thunks to hold the inner nested arrays, but I resent the need for this.

       

      In the present instance, there would appear to be solutions that broadcast without the need for such complexity.

       

      = COUNTIFS(lookup1,distinct1,lookup2,distinct2)

"where"
distinct1
= UNIQUE(lookup1)

distinct2
= TOROW(UNIQUE(lookup2))

       

      or, using the insider beta version of Excel

       

      = PIVOTBY(lookup1, lookup2, data, COUNTA,,0,,0)

       

       

      • anshul_marele's avatar
        anshul_marele
        Copper Contributor
        Jul 28, 2024

        PeterBartholomew1 thanks

        but can you please rewrite the 1st formula with actual cell ranges 

    • SergeiBaklan's avatar
      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.

Resources