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

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H
1	1	4	5	7	TRUE	1	2	1
2	2	3	4	4	FALSE	0	5	4
3	1	2	8	4	FALSE	0	7	7
4	1	2	8	54	FALSE	0
5	2	1	8	3	FALSE	0
6	2	3	7	5	TRUE	1
7	1	54	7	4	TRUE	1
8	35	4	6	5	FALSE	0
9	6	7	6	5	FALSE	0
10	6	8	4	3	FALSE	0
11	35	35	5	5	FALSE	0
12	24	35	4	5	FALSE	0
13	24	35	5	3	FALSE	0
14	1	2	5	7	TRUE	1
15	2	1	5	4	FALSE	0
16	1	2	5	7	TRUE	1
17					5	5	5
1aa

Excel 2003

Cell Formulas
Range	Formula
E17	=COUNTIF(E1:E16,TRUE)
F17	=SUM(F1:F16)
G17	=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))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Did you try Andrew's suggestion> See G17.

Unless you must use MMult, you could look at the above.

 

[planetpj]

Dave I did and Yes Andrews works well. I put in the wrong link
http://www.mrexcel.com/forum/showthread.php?t=519767

 

[Domenic]

Dave I did and Yes Andrews works well. I put in the wrong link
http://www.mrexcel.com/forum/showthread.php?t=519767

The formula I offered in the above link evaluates each row within the set of data separately, whereas your formula evaluates the entire set of data. So it seems to me that Andrew has given you the solution that you're looking for.

 

[barry houdini]

I think you will still have problems with duplicates with all the solutions proposed so far, e.g. if C9 is changed to 7 then row 9 will be counted......but it doesn't contain all the numbers - either 1,4 and 7 or 2,5 and 7

If you have duplicates and you want to avoid that then try this formula

=SUMPRODUCT(((MMULT((COUNTIF(OFFSET(A1:D1,ROW(A1:D16)-ROW(A1),0),{1,4,7})>0)+0,{1;1;1})=3)+(MMULT((COUNTIF(OFFSET(A1:D1,ROW(A1:D16)-ROW(A1),0),{2,5,7})>0)+0,{1;1;1})=3)>0)+0)

that will only count a row if it contains either set of 3 numbers in their entirety. Clearly for these numbers it's not possible for any single row to contain both sets.....but if you change the numbers (or include more columns) then note that this version only counts any one row once - even if it was to contain more than one set of numbers

 

[Domenic]

Barry,

Sorry, I forgot about your post regarding duplicates.

 

[planetpj]

Thanks to everyone who spent time on this post! Thanks to Andrew who made the formula compact! Thanks to Dave Patton for his take! Barry the formula is great and your absolutly right with a larger data set there may be duplicates! Domenic I understood the other post that I put the link too, I was just referencing you or someone else would get an idea or have some of that magic that you seem to put in all of your posts. I am still amazed at the posts that come out of here!

Thanks you all again!