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.

CodePudding user response：

Just an afterthought to this, I think the accepted answer is optimal but I did wonder if you could solve it using only countifs. The answer is yes, but you have to force the array arguments to be the same size and shape otherwise you get the 'array arguments different sizes' error so in J2:

=ArrayFormula(countifs(B2:F6,mod(sequence(5,10,0),10) 1,row(B2:F6) B2:F6-B2:F6,int(sequence(5,10,0)/10) 2))

Note the the total number of outcomes column gives the number of different outcomes using the formula below in H2:

=ArrayFormula(mmult(sign(J2:S6),sequence(10,1,1,0)))