Hello, I have a formula that finds the most common value in a row (we will call the most common value x). I then use the COUNTIF formula to count the number of occurrences of the value x.

However, My table is 9 rows deep, and 100 columns long. And the data in this table is from other areas of the worksheet, thus all cells in the table have formula in.

My problem is that the formula to find x, counts the blank cells. This means that x is always blank.

eg.

a, a, b, " " , " ", " " - instead of saying x is a because its the only value appearing twice, the formula says the most common value is " " and that it appears three times.



Here is the formula to find x:

=IFERROR((INDEX($B13:$L13,AGGREGATE(15,6,COLUMN($B$13:$L$13)-1/(COUNTIF($B13:$L13,$B13:$L13)=MAX(INDEX(COUNTIF($B13:$L13,$B13:$L13),))*(COUNTIF($M13:M13,$B13:$L13)=0)),COLUMN(A1)))),"")

Again, It counts the blank cells because they have formula in. Anyway to stop it counting these cells. Thanks

M is a helper column which is a series of 'x's.



edit: i tried to include an example workbook but it stopped working all together. really struggling with this.