Identify multiple mode values, excluding multiple user defined values from the results

Hello,

Currently, I have a workbook that looks at a large range of values and returns the 1st, 2nd, 3rd and 4th mode values in the range.

The addition I am trying to make is to allow a user to define values to exclude from these mode calculations. I have attached a sample workbook with some general information including my current formulas and the desired results (in green) I am trying to achieve. There is no significance to the numbers I have in the example. The actual numbers are 1-5 digits. I greatly appreciate any assistance. Thank you.

Drew

Try this in D2:

=MODE(IF((COUNTIF($A$2:$A$11,$D$6:$L$11)=0)*(COUNTIF($C$2:C2,$D$6:$L$11)=0)*($D$6:$L$11<>""),$D$6:$L$11))

Copy cross.

Originally Posted by kvsrinivasamurthy

ARRAY formula in E2 then drag across.

=MODE(IF(($D$6:$L$11="")+(COUNTIF($D$2:D2,$D$6:$L$11)>0)+(COUNTIF($A$1:$A$11,$D$6:$L$11)>0),"",$D$6:$L$11))

Originally Posted by Phuocam

Try this in D2:

=MODE(IF((COUNTIF($A$2:$A$11,$D$6:$L$11)=0)*(COUNTIF($C$2:C2,$D$6:$L$11)=0)*($D$6:$L$11<>""),$D$6:$L$11))

Copy cross.

Both work great! Thank you both

Drew