# Formula to count values in each row of a spill array

#### TonyD1016

##### Board Regular
I've come across another issue related to my probability simulator.

I'm using the following:
Excel Formula:
``=RANDARRAY(\$A\$4,6,1,6,TRUE)``
to generate a row of numbers to represent a series of dice rolls The row argument is a cell in which I type the number of series I want to simulate.

What I am looking for is a formula that will check each row and return counts of the results to a given criteria, such as all results equal to one, or greater than 1, or equal three. Preferably this formula would be some kind of sequence that automatically spills down to match the size of the test array.

So far the only thing I could think to try was =COUNTIF(INDEX(A5#,SEQUENCE(ROWS(A5#)),0),">1"), but this just returns an array of #VALUE! errors equal in size to the number of tests

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Fluff

##### MrExcel MVP, Moderator
Can you post some sample data & expected results. It's not very clear what you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

#### TonyD1016

##### Board Regular
Oh that's handy.
Here is a sample of what I am working with:
prop calc.xlsx
ABCDEFG
1
2
3
45Tests
52314662
63332361
75524463
83225152
93562342
Sheet2
Cell Formulas
RangeFormula
A5:F9A5=RANDARRAY(\$A\$4,6,1,6,TRUE)
G5:G9G5=COUNTIF(A5:F5,">=5")
Dynamic array formulas.

The above generates a series of random results from 1 to 6, with each row counting as a "test." At the end of each row I have a COUNTIF returning the number of times a result of 5 or 6 appears in each row.
What I'm trying to do is come up with a formula that will automatically go through each row the array produces.

#### Fluff

##### MrExcel MVP, Moderator
Ok, thanks for that.
Excel Formula:
``=MMULT(--(A5#>=5),SEQUENCE(COLUMNS(A5#),,,0))``

#### TonyD1016

##### Board Regular

Ok, thanks for that.
Excel Formula:
``=MMULT(--(A5#>=5),SEQUENCE(COLUMNS(A5#),,,0))``
That works perfect.
I had come across MMULT before but only in the context of counting specific values. What purposes does the "--" at the beginning serve?

#### Fluff

##### MrExcel MVP, Moderator
It;s called a Double Unary & it coerces the True/False results from `(A5#>=5)` into 1s & 0s

#### TonyD1016

##### Board Regular
Outstanding. Thank you again Fluff.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,299
Messages
5,853,152
Members
431,549
Latest member
NnAa

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