Oring MMULT?

[planetpj]

Good Morning,

I am trying to figure out a way to OR isnumber match inside my MMULT formula. I can get the formula to work if I use the same line twice but I am trying toi condense the formula to a more compact one could anyone please help me condense this?

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A1:D16,{2,5,7},0)),{1;1;1;1})=3))+SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A1:D16,{1,4,7},0)),{1;1;1;1})=3))

This is the idea that I am looking for
=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A1:D16,{2,5,7}*(--ISNUMBER(MATCH(A1:D16{1,4,7},0)),{1;1;1;1})=3))

Thanks in advance!

Book1
	A	B	C	D
1	1	4	5	7
2	2	3	4	4
3	1	2	8	4
4	1	2	8	54
5	2	1	8	3
6	2	3	7	5
7	1	54	7	4
8	35	4	6	5
9	6	7	6	5
10	6	8	4	3
11	35	35	5	5
12	24	35	4	5
13	24	35	5	3
14	1	2	5	7
15	2	1	5	4
16	1	2	5	7
Sheet1

 

[GlennUK]

Can you describe what you are trying to achieve with your formula?

 

[planetpj]

The formula is trying to count if {2,5,7} or {1,4,7} occur in a row. It will total up to 5. The formula below works but I know that there is a way to condense it.

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A1:D16,{2,5,7},0)),{1;1;1;1})=3))+SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A1:D16,{1,4,7},0)),{1;1;1;1})=3))

 

[shg]

I think the correct syntax is

=SUMPRODUCT( --(MMULT(ISNUMBER(MATCH(A1:D16, {2,5,7})) * ISNUMBER(MATCH(A1:D16, {1,4,7}, 0)), {1;1;1;1}) = 3))

... but it calculates something different to your first formula, i.e., the cases where the conditions are simutaneously true versus summing the cases where either condition is true.

 

[planetpj]

shg4421,

Thanks for the response, I am getting a 0 with that formula?

 

[Andrew Poulsom]

You would use + for OR, like this:

=SUMPRODUCT(--(MMULT(ISNUMBER(MATCH(A1:D16,{2,5,7},0))+ISNUMBER(MATCH(A1:D16,{1,4,7},0)),{1;1;1;1})>3))

but that won't work because you are testing each array separately. With the formula above row 15 passes the test because it's 2+2.

 

[shg]

Thanks for the response, I am getting a 0 with that formula

Yup -- the conditions are mutually exclusive, so it will always return 0. I see what you want to do, and the formula I posted doesn't do it.

 

[Andrew Poulsom]

Only slightly more comact:

=SUMPRODUCT((MMULT(--ISNUMBER(MATCH(A1:D16,{2,5,7},0)),{1;1;1;1})=3)+(MMULT(--ISNUMBER(MATCH(A1:D16,{1,4,7},0)),{1;1;1;1})=3))

 

[barry houdini]

In your example some rows have repeated numbers - given that your formula would count a row that contains, say, 1,1,7 and 8....I assume you don't want to do that?

 

[planetpj]

Thanks Barry No I do not,
What I would like to do is count if the row if it has a 2,5, or 7 in it or if the row has a 1,4, or 7. It has to have all 3 value either the 257 or the 147. Domenic did somehing like this in this post But with AlphaNumeric Characters.

http://www.mrexcel.com/forum/showthread.php?t=524778