Dave Patton
Well-known Member
- Joined
- Feb 15, 2002
- Messages
- 6,355
- Office Version
- 365
- 2010
- Platform
- Windows
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.
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.