Conditional mode with criteria

In order to estimate the conditional mode consisting of one or more criteria we can use the Excel built-in functions, the IF function and the MODE function together to estimate the conditional mode with criteria.

Generic Formula


How does the formula work?

  1. The MODE function in Excel does not contain any specific format to apply the criteria parameter. Therefore, if you give a range to this parameter then it returns the value which is the most frequently occurring value in the given range.
  2. To apply the criteria parameter we make use of the Excel IF function which tests each data in a group for a match with the value and returns the result as an array of TRUE and FALSE values. For example {TRUE; FALSE; TRUE}
  3. The IF function returns the corresponding numeric values for each TRUE result and there is no change for FALSE values. The MODE function now returns the numeric value that is most frequently occurring in the list.


Note that if additional criteria to be added then use a nested IF function than using a single IF as shown below.

=MODE( IF(criteria_1,IF(criteria _2,data)))


Let us consider an example of female and male employees who contain a specific number of datasheets and let us use MODE function along with an IF function to get the final result, which returns the most often occurring numeric value( datasheets).

Figure 1. The result obtained for a conditional mode for given criteria.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment