Formula to count values in each row of a spill array

[TonyD1016]

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

I'm using the following:

=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

 

[Fluff]

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]

Oh that's handy.
Here is a sample of what I am working with:

prop calc.xlsx
	A	B	C	D	E	F	G
1
2
3
4	5	Tests
5	2	3	1	4	6	6	2
6	3	3	3	2	3	6	1
7	5	5	2	4	4	6	3
8	3	2	2	5	1	5	2
9	3	5	6	2	3	4	2
Sheet2

Cell Formulas
Range	Formula
A5:F9	A5	=RANDARRAY($A$4,6,1,6,TRUE)
G5:G9	G5	=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]

Ok, thanks for that.
How about

=MMULT(--(A5#>=5),SEQUENCE(COLUMNS(A5#),,,0))

 

[TonyD1016]

Ok, thanks for that.
How about

=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]

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

 

[TonyD1016]

Outstanding. Thank you again Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.