How to make an arrayformula count values by row?

user_17149901 0
Viewed 300

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.

Screenshot of the test table

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!

countif array-formulas google-sheets-formula google-sheets count
2 Answers

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

enter image description here

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)))}

enter image description here

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)})

enter image description here

demo sheet

user_56326290

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

enter image description here

user_38949170
Related