Forum Discussion

anshul_marele's avatar
anshul_marele
Copper Contributor
Jun 28, 2024
Solved

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

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

      anshul_marele 

      Sorry but I trap 'actual cell ranges' as errors and delete the formula :xd:.  The correct direct reference will depend on where one happens to place the data.  From your earlier picture I have gone with the idea that 'lookup1' should be the column of data lying below the text heading 'lookup1'.  That means the references, will be

       

      Lookup2
=$I$3:$I$25

Lookup1
=$J$3:$J$25

       

      Distinct1 and Distinct2 are not ranges, so a range reference is not appropriate.

       

Resources