Results 1 to 13 of 13

Thread: Help needed to count duplicates but derived by a column of values

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,970
    Horrible formula in cell L3, copied across:
    =LAMBDA(vals,groups,group,LET(b,MAX(BYROW(--(TRANSPOSE(vals)=FILTER(vals,groups=group)),LAMBDA(a,SUM(a)))),IFERROR(IF(b>1,b,0),0)))($I$2:$I$13,$G$2:$G$13,L$1)
    all because COUNTIF(S) won't work with arrays.
    Nicer formula in cell L4:
    =MaxEqualValuesCount($I$2:$I$13,$G$2:$G$13,L$1)
    which is the same formula, but used in a Name. You get a hint of what goes where:

    2023-02-07_223319.jpg

    and I've assumed if, for example, there are 5 values made up of 2 unique values, 3 of 1 and 2 of the other, you'll want the maximum only (3).
    It would be good if someone could come up with a more elegant solution.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •