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

#### Stephen_IV

##### Well-known Member
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.

Stephen
Book1
ABCDEFGHIJ
2xxxxxxyyyyyy12
3xxxxxxyyyyyy1434141
4xxxxxxyyyyyy17272727272
5xxxxxxyyyyyy145
6xxxxxxyyyyyy145454568
7xxxxxxyyyyyy1494949495252
8xxxxxxyyyyyy1757575
9xxxxxxyyyyyy1444444447514
10xxxxxxyyyyyy1444444444444
11xxxxxxyyyyyy14444444355
12xxxxxxyyyyyy1444444234343
13xxxxxxyyyyyy14444799
14xxxxxxyyyyyy14444443232
15xxxxxxyyyyyy144447910
16xxxxxxyyyyyy13030302626
17xxxxxxyyyyyy13044232222
18xxxxxxyyyyyy12222
19xxxxxxyyyyyy12222
Sheet1

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Oaktree

##### MrExcel MVP
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

##### MrExcel MVP
What is it that you're trying to count?

#### Stephen_IV

##### Well-known Member
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

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

#### Stephen_IV

##### Well-known Member
Then that is not included in the count. It has to be all the same values in the row.

#### Oaktree

##### MrExcel MVP
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

#### pgc01

##### MrExcel MVP
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

##### Well-known Member
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!

Replies
9
Views
199
Replies
3
Views
215
Replies
2
Views
341
Replies
9
Views
475
Replies
3
Views
246

1,191,219
Messages
5,985,338
Members
439,958
Latest member
qb0000

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back