Count groups of text with multiple ranges

[Dave Patton]

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.

 

[Aladin Akyurek]

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

 

[Dave Patton]

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.