How to make an arrayformula count values by row?

Question

I have tried many approaches but I can't seem to figure out a solution to a fairly simple problem with an array formula it seems:

I have a sheet where I get results of individual results of runs of a certain scientific experiment by row. Next, I'd like to count the occurrence of each output value. Output values can be a number from 1 to 10 so I'd like to individually count how many times the output has been per value.

Also, I'd be then summing up how many individual outputs have been generated in total. So I'd be counting the number of unique outputs within a row.

Now my issue is that when using Arrayformula the way I normally use it it doesn't work because it would count and sum up everything for the whole Matrix specified. However, I just want the current row to be counted. I tried using ROW to get there, but i failed. I also tried some things like =ARRAYFORMULA(SUMIF(IF(COLUMN(B1:F1),ROW(B2:F5)),ROW(B2:F5),B2:F5)) to understand how to apply the logic, but I fail to get it working as COUNTIF version if itself.

Test Sheet

Any help and ideas would be highly appreciated!

Answer 1

in H2 use:

=INDEX(MMULT(IF(INDIRECT("B2:F"&MAX((ROW(A2:A)*(A2:A<>""))))=""; 0; 1); 
 FLATTEN(COLUMN(B:F)^0)))

in J2 use:

=INDEX(ARRAY_CONSTRAIN(QUERY(QUERY({IFERROR(SPLIT(FLATTEN({ROW(A2:A)&"×"&B2:F}); "×")); 
 {SEQUENCE(10)*9^9\SEQUENCE(10)}}; 
 "select count(Col1),Col1 where Col2 is not null group by Col1 pivot Col2"); 
 "where Col11 < 100000 offset 1"; 0); 9^9; 10))

---

update:

H1:

={"Total No. of outcomes"; ARRAYFORMULA(IFNA(VLOOKUP(ROW(A2:A); 
 QUERY(SPLIT(UNIQUE(FLATTEN(ROW(B2:F)&"×"&B2:F)); "×"); 
 "select Col1,count(Col1) where Col2 is not null group by Col1"); 2; 0)))}

J1:

=INDEX({"Occurence of "&SEQUENCE(1; 10); ARRAY_CONSTRAIN(QUERY(QUERY({IFERROR(
 SPLIT(FLATTEN({ROW(A2:A)&"×"&B2:F}); "×")); {SEQUENCE(10)*9^9\SEQUENCE(10)}}; 
 "select count(Col1),Col1 where Col2 is not null group by Col1 pivot Col2"); 
 "where Col11 < 999999 offset 1"; 0); 9^9; 10)})

demo sheet