Difficult counting procedure

[Shane]

Hello Group!

I hope someone can simplify a counting procedure for me. I
have a data set that looks like this -

A B C

X Y Z
Y Z X
Z B Y
Z X U
Z Y X

I need to count occurances that contain XYZ in ANY order.
That could be ZYX, XYZ, YZX. There are 6 possible
combinations. Currently I use a helper column and perform
a count on each row. Then do a count on the helper column
to get my result. Based on the example, the result would
be 3. I have been trying to come up with a single formula
to do this. The problem is how to account for all of the
possible combinations in a Sumproduct function. Any help
would be greatly appreciated.

Shane

 

[Harlan Grove]

I hope someone can simplify a counting procedure for me. I
have a data set that looks like this -

A B C

X Y Z
Y Z X
Z B Y
Z X U
Z Y X

I need to count occurances that contain XYZ in ANY order.
That could be ZYX, XYZ, YZX. . . .

If your data were in A3:C7, and the values to be matched were in A1:C1, try the
array formula

=SUM(--(MMULT(--((A3:A7=A1:C1)+(B3:B7=A1:C1)+(C3:C7=A1:C1)=1),
TRANSPOSE(COLUMN(A1:C1))^0)=3))

This gives 3 for the sample data above.

 

[Biff]

Hi Shane,

A couple of possibilities:

Combine into 1 formula a SUMPRODUCT function for each of
the combinations - something like:

=SUMPRODUCT((A:A="X")*(B:B="Y")*(C:C="Z"))+
SUMPRODUCT((A:A="X")*(B:B="Z")*(C:C="Y"))+
...

This will be a very long formula but it will do what you
want.

The other possibility is to use a seperate SUMPRODUCT for
each combination and then sum the results. Not exactly
what you wanted.

Biff

 

[Biff]

Hi Shane,

Here's a shorter version:

=SUMPRODUCT((A14:A17={"X","X","Z","Z","Y","Y"})*
(B14:B17={"Y","Z","Y","X","Z","X"})*
(C14:C17={"Z","Y","X","Y","X","Z"}))

The tricky part here is getting the matrix of the arrays
correct.

Biff

 

[Shane]

Biff - Thanks, I do like the 'shorter' version.

Shane

 

[Harlan Grove]

Here's a shorter version:

=SUMPRODUCT((A14:A17={"X","X","Z","Z","Y","Y"})*
(B14:B17={"Y","Z","Y","X","Z","X"})*
(C14:C17={"Z","Y","X","Y","X","Z"}))

The tricky part here is getting the matrix of the arrays
correct. ....

....

Biff's formula doesn't work for me with huge column ranges. I created test
data in A1:C20000 using =CHAR(INT(65+26*RAND())) in each cell. I entered
Biff's formula both as a regular formula and as an array formula, and it
always evaluates to 0 (rather than error).

While it's monstrous, the following variation on my original formula against
data in A1:C20000 with values to match in E1:G1 does seem to work. It's
still an array formula.

=SUM(--(MMULT(--((OFFSET(A1,0,0,5000,1)=E1:G1)
+(OFFSET(B1,0,0,5000,1)=E1:G1)+(OFFSET(C1,0,0,5000,1)=E1:G1)=1),
TRANSPOSE(COLUMN(E1:G1))^0)=3))
+SUM(--(MMULT(--((OFFSET(A1,5000,0,5000,1)=E1:G1)
+(OFFSET(B1,5000,0,5000,1)=E1:G1)+(OFFSET(C1,5000,0,5000,1)=E1:G1)=1),
TRANSPOSE(COLUMN(E1:G1))^0)=3))
+SUM(--(MMULT(--((OFFSET(A1,10000,0,5000,1)=E1:G1)
+(OFFSET(B1,10000,0,5000,1)=E1:G1)+(OFFSET(C1,10000,0,5000,1)=E1:G1)=1),
TRANSPOSE(COLUMN(E1:G1))^0)=3))
+SUM(--(MMULT(--((OFFSET(A1,15000,0,5000,1)=E1:G1)
+(OFFSET(B1,15000,0,5000,1)=E1:G1)+(OFFSET(C1,15000,0,5000,1)=E1:G1)=1),
TRANSPOSE(COLUMN(E1:G1))^0)=3))

That is, the monster array formula above gives the same result as entering
the following array formula in D1,

=--AND(CHAR(SMALL(CODE(A1:C1),{1,2,3}))={"X","Y","Z"})

and filling D1 down into D220000, then summing D120000.

 

[Harlan Grove]

....

Tried it again. Biff's formula does work. I made a typo when deleting
whitespace after pasting his formula into a cell. Sorry, Biff.

But it got me to thinking about alternatives. The following also works, and
it doesn't involve matching permutations.

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("XYZ",A1:A20000,""),
B1:B20000,""),C1:C20000,"")=""))

 

[Biff]

Hi Harlan,

Good alternative for not having to manually define all the
combinations - especially if there are a large number of
possible combinations.

Biff