Share via

Excel using an array in countif

Anonymous
2024-10-13T16:07:59+00:00

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.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2024-10-14T20:40:20+00:00

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

Image

Was this answer helpful?

2 people found this answer helpful.
0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2024-10-13T18:39:15+00:00

No, but try

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

Was this answer helpful?

1 person found this answer helpful.
0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-10-14T14:33:36+00:00

    Thanks, yes that works but you probably knew that, have not had a chance to take it all apart but will
    do.
    Thanks,

    RD

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-10-14T14:20:00+00:00

    Could you give an example of what you mean.

    Was this answer helpful?

    0 comments
  3. Ashish Mathur 102K Reputation points Volunteer Moderator
    2024-10-13T23:04:15+00:00

    Hi,

    Share an example and show the expected result. Share data in a format that can be pasted in an MS Excel file.

    Was this answer helpful?

    0 comments