Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Count groups of text with multiple ranges

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Count groups of text with multiple ranges

    With range A3:P3 how many groups of three
    (any order) occur?
    Example Apple Carrot Orange =1

    I put the criteria in a range A12:A14 and named it rI. The criteria are Apples, Oranges, Carrots.

    =(SUM(IF(COUNTIF(A3:C3,rI)=1,1,0))=3)*1 gives result for first group.

    What formula will gives the result across the range?

    I can get the result with several brute force
    methods.
    Putting multiple versions of the above (with a3:C3 shifted) is a bit of a monster.


    =(SUM(IF(COUNTIF(A3:C3,rI)=1,1,0))=3)*1+(SUM(IF(COUNTIF(B3:D3,rI)=1,1,0))=3)*1 .. etc

    Thanks in advance.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Dave,

    Condider the following sample

    {"Apples","Oranges","Carrots","Oranges","Apples","Carrots","pears","oranges","vegetables","apples","carrots","oranges"}

    in A3:L3 (I didn't make it up to P3 ).

    The conditions (the target 'unordered' triplet) for which we want a count of occurrences in A3:L3 are in A12:A14, that is,

    {"Apples";"Oranges";"Carrots"}.

    A12:A14 is named as rl.

    In A4 array-enter and copy across as far as needed:

    =IF(COUNTA(A3:C3)=3,IF(SUM(1/(COUNTIF(A3:C3,A3:C3)))=3,(COUNT(MATCH(A3:C3,rl,0))=3)+0,0),0)

    In A5 enter:

    =SUM(A4:L4)

    In order to array-enter a formula (I know you know all this), hit control+shift+enter at the same time, not just enter.

    Note. I didn't seek to collapse the main formula into a single cell-formula nor looked for such an alternative.

    Regards,

    Aladin


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Aladin

    Thanks for the alternative.
    It gives the same answer as my formula and it includes one additional factor.

    Thank you

    At this time, I believe if someone really needs such a feature for a range A3:P?, someone could probably develop a UDF.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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