Forum Discussion
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)))))
Need one array formula.
- =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/
13 Replies
=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.
Very thanks OliverScheurich
But why to complicate a simple logic, if it is possible in Google Sheets then would also possible in Excel."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)
