Error in LET formula using a constructed array

Question

I have some data where each record is date stamped. I wanted to count the number of records for each year.

The formula that failed is =LET(d,A1:A135,y,YEAR(d),u,UNIQUE(y),s,SORT(u),c,COUNTIF(y,s),c). Instead of producing an array of 14 counts, it produced an array of 135 #VALUE.

I arrived at this formula using the following incremental approach:

• Delete all the columns except the date stamp one. Data is in A1:A135.
• Enter =YEAR(A1:A135) IN B1. B1:B135 contains correct year values.
• Enter =UNIQUE(B1:B135) in C1. C1:C14 contains one entry for each year.
• Enter =SORT(C1:C14) in D1. D1:D14 contains sorted years.
• Enter =COUNTIF(B1:B135,D1:D14) in E1. E1:E14 contains counts for each year in D.
• Eliminate the intermediate calculations: enter =COUNTIF(B1:B135,SORT(UNIQUE(B1:B135))) in F1. Columns E and F match.
• Do everything in one formula: enter =LET(d,A1:A135,y,YEAR(d),u,UNIQUE(y),s,SORT(u),c,COUNTIF(B1:B135,s),HSTACK(s,c)) in G1. Columns G and H match columns D and E.
• Eliminate hard-coded values: enter =LET(d,A1:A135,y,YEAR(d),u,UNIQUE(y),s,SORT(u),c,COUNTIF(y,s),HSTACK(s,c)) in K1. The only change to this formula is replacing the hard-coded value for COUNTIF argument 1 with an internal named array that is obviously identical to the hard-coded one (tested and confirmed). Instead of producing a 2C x 14R array as in G and H, this produced a 2C x 135R array. The second column contained 135 #VALUE. The first column contained 14 unique year values and 121 #N/A.

Removing the call to HSTACK produced the formula and result describe at the top of this message. In the successful and failing formulas, the first argument to COUNTIF is a 1C x 135R array. Why does it matter if it is hard-coded or named? And how does that create so many rows when the second argument is unchanged with only 14 entries?

Answer 1

@Barry Schwarz

Sorry, for having been unclear. It's not the UNIQUE function per se but it's the first argument in COUNTIF that expects a regular or spilled cell range. B1:B135 or B1# will both work.

But a variable y, based on YEAR( d ), returns an 'in memory' array rather then a worksheet range (regular or spilled). This results in an array with135 value errors.

Similarly, if you would use COUNTIF( u, s ) you'll get an array with14 value errors.

Edit:

This one will work though.

=LET(

d, A1:A135,

y, YEAR( d ),

u, UNIQUE( y ),

s, SORT( u ),

c, BYROW( s, LAMBDA(r, COUNTA(FILTER( y, y = r)))),

HSTACK(

s,

c

)

)

Answer 2

[Edited:] Ok. Sorry that idea didn't work.

Answer 3

@Barry Schwarz

Not sure that my wording is 100% correct, but when used within a formula, UNIQUE returns an 'in memory' array. COUNTIF expects references to worksheet ranges (regular or spilled).