Thanks:  0
Likes:  0

# Thread: Count groups of text with multiple ranges

1. 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

2. 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,

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.

## User Tag List

#### Posting Permissions

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