MODE with SUMPRODUCT formula

Question

I am trying to find a mode of weekdays (Monday, Tuesday, etc.) within a date range by using the formula:

=ARRAYFORMULA(MODE(SUMPRODUCT(--(AgendaData!$A:$A>=A$6),--(AgendaData!$B:$B<=B$6),AgendaData!$F:$F)))

The error reads: "MODE cannot produce a result. No values occur more than once."

I think it has something to do with the F column being in text format. I want the result to show the mode of the weekday in text. I wonder what I am doing wrong here.

Answer 1

You can find the mode of text values by converting them to numbers, finding the mode, and converting back. It's convenient to use a three-column lookup table for this, e.g.,

Monday      1   Monday
Tuesday     2   Tuesday
Wednesday   3   Wednesday
Thursday    4   Thursday
Friday      5   Friday
Saturday    6   Saturday
Sunday      7   Sunday

Let's say this table is in columns I:K, and your data is in column F. Then the following finds its mode:

=vlookup(mode(arrayformula(iferror(vlookup(F:F, I:J, 2, false)))), J:K, 2)

Here, arrayformula(iferror(vlookup... is conversion to numeric, using the part I:J of the table, and ignoring blank cells and anything that isn't a day of week. After mode is applied, vlookup converts the number back to text using the part J:K of the table.

Answer 2

To check it out, you may use formula:

=filter(AgendaData!$F:$F,AgendaData!$A:$A>=A$6,AgendaData!$B:$B<=B$6)

You'll see all matching values from column F and will know exactly if some values occur more than once.