MMULT looking to count all matches in a row including a single value

[Stephen_IV]

I am looking to count all matches in a row including a single value. The count I am looking for is 6. It does not matter where the number starts in the row. I know that this is a job for mmult but just can't get it.

Thanks in advance
Stephen

Book1
	A	B	C	D	E	F	G	H	I	J
1	L Name	F Name	DOB	Grade	02	03	04	05	06	07
2	xxx	xxx	yyyyyy	1	2
3	xxx	xxx	yyyyyy	1	43	41	41
4	xxx	xxx	yyyyyy	1	72	72	72	72	72
5	xxx	xxx	yyyyyy	1	45
6	xxx	xxx	yyyyyy	1	45	45	45	68
7	xxx	xxx	yyyyyy	1	49	49	49	49	52	52
8	xxx	xxx	yyyyyy	1	75	75	75
9	xxx	xxx	yyyyyy	1	44	44	44	44	75	14
10	xxx	xxx	yyyyyy	1	44	44	44	44	44	44
11	xxx	xxx	yyyyyy	1	44	44	44	43	5	5
12	xxx	xxx	yyyyyy	1	44	44	44	23	43	43
13	xxx	xxx	yyyyyy	1		44	44	7	9	9
14	xxx	xxx	yyyyyy	1		44	44	44	32	32
15	xxx	xxx	yyyyyy	1		44	44	7	9	10
16	xxx	xxx	yyyyyy	1		30	30	30	26	26
17	xxx	xxx	yyyyyy	1		30	44	23	22	22
18	xxx	xxx	yyyyyy	1			2	2	2	2
19	xxx	xxx	yyyyyy	1			2	2	2	2
Sheet1

 

[Oaktree]

It's not clear (at least to me) how the 6 you're looking to count is calculated...maybe you can walk us through that?

 

[Domenic]

What is it that you're trying to count?

 

[Stephen_IV]

Thanks, I will try and give some clarification. I am trying to count rows that have the same number throughout. If a row has a single value then I want that in the count. We are trying to count how many students start and end up in the same place after x amount of years.

 

[Oaktree]

What if the starting and ending value are the same, but the middle numbers are different?

 

[Stephen_IV]

Then that is not included in the count. It has to be all the same values in the row.

 

[Oaktree]

Until someone smarter than me comes up with one formula to do it, it's easy enough with a helper column:

L2 =--(COUNTIF(E2:J2,MAX(E2:J2))=COUNTA(E2:J2)) copied down
M1 =SUM(L:L) gives your total

 

[pgc01]

Hi Stephen

Try:

=SUM(--(MMULT((E2:I19<>"")*(F2:J19<>"")*(F2:J19<>E2:I19),TRANSPOSE(--(COLUMN(E2:I2)>0)))=0))
entered with CSE.

The result is, however, 7 and not 6. The difference is that it's counting row 5, that has only a value 45. Did you not consider it by mistake or am I missing something?

 

[Stephen_IV]

pgc01,

Thanks so much. You are right, it seems that row 5 has a value of 45 which you are right it should be 7. On my original sheet I had 45 and 46 in that row I don't know why the HTML maker did not show it, oh well.

Thanks again it works perfectly!