My problem is similar to this entry:
How to Determine the Mode of a group of cells
How can I find the modal value if the range to be searched for is not one columns, but in several columns?
Here an example:
enter image description here
Formulas should be used to calculate the values. Thanks in advance!
I have tried to use this formulas but they only work if the category is in one column:
I2:
=COUNTIF(A2:A10,”?*”)
J2:
IF(ROWS(J$2:J2)<=$I$2,INDEX($A$2:$A$10,SMALL(IF(LEN($A$2:$A$10)>0,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(J$2:J2))),””)
K2:
=IF(ROWS(K$2:K2)<=$I$2,INDEX($E$2:$E$10,MODE(IF(COUNTIF(OFFSET($A$2:$A$10,,,ROW($A$2:$A$10)
Jerry 0698 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.