Forum Discussion

Copper Contributor

Jun 18, 2020

Solved

Least common value in a range with blank cells

I have written a formula to find the least common value in a column however it doesn't work when blank cells are in the column, so I was wondering how to get around this?

This is my formula:

=IFERROR(INDEX(AF9:AF32,MATCH(MIN(COUNTIF(AF9:AF32,AF9:AF32)),COUNTIF(AF9:AF32,AF9:AF32),"No Least Common")),"No Least Common")

excel

Formulas and Functions

Detlef_Lewin
Jun 18, 2020
jsinghbest
Try this.

=INDEX(AF9:AF32,MATCH(AGGREGATE(15,6,1/(1/COUNTIFS(AF9:AF32,AF9:AF32)),1),COUNTIFS(AF9:AF32,AF9:AF32),0))

Edit: Shortened the formula a bit.

4 Replies

Detlef_Lewin
Silver Contributor
Jun 18, 2020
jsinghbest
There is a little error in you formula:
=IFERROR(INDEX(AF9:AF32,MATCH(MIN(COUNTIF(AF9:AF32,AF9:AF32)),COUNTIF(AF9:AF32,AF9:AF32),0)),"No Least Common")
- jsinghbest
  Copper Contributor
  Jun 18, 2020
  Detlef_LewinThanks, I have made those changes which has made an improvement.
  
  However I've tested it out and the output is "0" when I include blank cells, rather than displaying the least common value of the cells that have values in them
  - Detlef_Lewin
    Silver Contributor
    Jun 18, 2020
    jsinghbest
    Try this.
    
    =INDEX(AF9:AF32,MATCH(AGGREGATE(15,6,1/(1/COUNTIFS(AF9:AF32,AF9:AF32)),1),COUNTIFS(AF9:AF32,AF9:AF32),0))
    
    Edit: Shortened the formula a bit.