Excel using an array in countif

Question

Hi, Is there a way I can convert an array to a range within a Let statement,

situation ; I have used =TOCOL(Table1) = J4# then FILTER(J4#, COUNTIF(J4#,J4#)= 1 ),

but is there a way to avoid the helper and get the TOCOL part directly into COUNTIF ?

=LET( toc, TOCOL(Table13), COUNTIF(toc,toc)) obviously this does not work but is there a work around?

Richard.

Answer 1

Hi. I usually don't comment. Your solution is in D1, but I would use the formula in D7

Answer 2

No, but try

=LET(col, TOCOL(Table1), counts, BYROW(col, LAMBDA(rw, SUM(--(col=rw)))), FILTER(col, counts=1, ""))

Answer 3

Thank you for the suggestion and I will bare keep it in mind for future use, I don't think ti was
quite what I needed which was to replicate ; COUNTIF( rnageA, rangeA ) , but for an array within a let statement. But thanks for the input.

Richard.

Answer 4

That's a lot shorter!