01 / 10MODE Picked UK 9; the Shop Sold More 8.5

StatisticsData QualityCOUNTIF

MODE Function in Excel: Find the Most Common Value

MODE Said the Best-Selling Boot Was a UK 9 and the Shop Ran Out of 8.5s in a Fortnight, Because Every Web Order Arrived With Its Size Typed as Text

6 Nov 20269 min read

UsesMODECOUNTIFVALUEINDEX MATCH

Brackenfold Boots reorders each style by the size that sold most. In October, MODE said the Fellside boot's was UK 9. The sales log said UK 8.5, seven pairs to six, and by 14 November the shop had none left.

Fellside boot, October 2026MODE sawSold
Sales counted917
Pairs of UK 8.517
Pairs of UK 956
Most common sizeUK 9UK 8.5

Scope. MODE, COUNTIF, COUNT, VALUE, INDEX, MATCH and SUMPRODUCT work in every version of Excel since 2010, and so do MODE.SNGL and MODE.MULT. XLOOKUP, UNIQUE and SORT need Excel 2021 or Microsoft 365.

Throughout, the 17 sales are in A1:E18: order in A, channel in B, colour in C, the size as received in D and the converted size in E. The count table is in G1:I6 and the two modes are in row 8.

01MODE Picked UK 9; the Shop Sold More 8.5

Brackenfold sells the Fellside boot in its shop and on its website. At each month-end the buyer reorders 12 pairs of the most common size and four of every other, so one formula decides where most of the money goes.

The sales list is two exports pasted one under the other and sorted by time. The reorder sheet takes the size from column D:

=MODE(D2:D18)

Seventeen Pairs, Two Channels, One Wrong Size

October 2026's sales of the Fellside boot at Brackenfold Boots, in A1:E18. Column D is the size as each channel sent it: numbers from the shop till, text from the web shop. Column E is the same size converted with VALUE. G1:I6 counts each size twice: H is what MODE could see in column D, I is every pair sold. Row 8 is the mode of each column.

ABCDEFGHI
1
Order
Channel
Colour
Size as received
Size
Size
MODE saw
Sold
2
S-1041
Shop
Brown
9
9
UK 8
2
2
3
W-5520
Web
Black
8.5
8.5
UK 8.5
1
7
4
S-1042
Shop
Brown
8
8
UK 9
5
6
5
S-1043
Shop
Black
9
9
UK 10
1
2
6
W-5524
Web
Brown
8.5
8.5
Total
9
17
7
W-5531
Web
Olive
10
10
8
S-1044
Shop
Brown
9
9
Most common
9
8.5
9
W-5537
Web
Brown
8.5
8.5
10
S-1045
Shop
Black
8.5
8.5
11
W-5542
Web
Black
9
9
12
S-1046
Shop
Olive
9
9
13
W-5548
Web
Brown
8.5
8.5
14
S-1047
Shop
Black
8
8
15
W-5553
Web
Brown
8.5
8.5
16
S-1048
Shop
Brown
10
10
17
W-5561
Web
Black
8.5
8.5
18
S-1049
Shop
Olive
9
9

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

It returned 9, and the buyer ordered 12 pairs of UK 9 and four of UK 8.5. Seven pairs of 8.5 had sold in October against six of 9, and the four new pairs were gone in a fortnight.

Scenario: Enter the 17 sales in A2:D18, with the web sizes typed as text (start each with an apostrophe, '8.5). Enter =MODE(D2:D18) in H8 and check that it reads 9.

Try it in the grid

02MODE Skips Every Size Stored as Text

The till sends each size as a number. The web shop sends it as text, so the eight web rows hold "8.5", "9" and "10" as words that look like numbers.

MODE, like SUM and AVERAGE, ignores text in a range it is given. It raises no error. It simply counts the numbers it can see, and in column D that was the nine shop sales.

=COUNT(D2:D18)                 9    numbers MODE can see
=COUNTA(D2:D18)                17   sales in the list
=SUMPRODUCT(--ISTEXT(D2:D18))  8    sizes stored as text

Among the shop sales alone, 9 really was the most common size: five pairs, against one of 8.5. Six of the seven 8.5s came from the website, and MODE never counted them.

SizeMODE sawSold
UK 822
UK 8.517
UK 956
UK 1012
Total917

Scenario: Enter =COUNT(D2:D18) in K2 and =COUNTA(D2:D18) in K3. Check that they read 9 and 17; any gap between them is text that MODE will skip.

Try it in the grid

03Convert the Sizes, Then Take the Mode

The fix is to turn every size into a number before counting. VALUE converts text that looks like a number and leaves real numbers alone, so one formula works on both channels' rows:

=VALUE(D2)

Fill it down to E18 and point the reorder sheet at the new column. =MODE(E2:E18) returns 8.5, and the count of numbers it can see is now 17.

On Microsoft 365 the helper column is optional. =MODE(VALUE(D2:D18)) converts the whole range inside the formula and returns 8.5 in one cell.

Decimal commas. VALUE reads text in your own regional format. On a PC set to a decimal comma, VALUE("8.5") returns #VALUE!. Use =NUMBERVALUE(D2,"."), which names the separator the text was written with.

The lasting fix is upstream: set the size column's type to a number in the web export, or in Power Query, so the text never reaches the sheet.

Scenario: Enter =VALUE(D2) in E2 and fill it down to E18. Enter =MODE(E2:E18) in I8 and check that it reads 8.5, then confirm =COUNT(E2:E18) reads 17.

Try it in the grid

04Count Every Size, Not Just the Winner

MODE returns one value and hides everything behind it. A count table shows how close the runner-up came, which is what a reorder actually needs to know.

With the sizes listed in G2:G5, count each one in the converted column:

=COUNTIF($E$2:$E$18,8.5)

The table reads 7 for 8.5 and 6 for 9. A lead of one pair is not a reason to put 12 pairs on one size and four on the next; the counts argue for an order closer to the sales.

Two checks belong beside the table. The first proves every sale was counted; the second counts how many sizes share the top count:

=SUM(I2:I5)=COUNTA(A2:A18)     TRUE
=COUNTIF(I2:I5,MAX(I2:I5))     1

On Microsoft 365, =SORT(UNIQUE(E2:E18)) in G2 spills the size list, and =COUNTIF(E2:E18,G2#) beside it counts every size, with no list to keep up to date.

Scenario: Enter =COUNTIF($E$2:$E$18,8.5) in I3 and the same for 8, 9 and 10 in I2, I4 and I5. Check that I3 reads 7 and that =SUM(I2:I5) reads 17.

Try it in the grid

05A Tie Goes to Whichever Came First

When two values share the top count, MODE returns the one that appears first in the range. It gives no sign that there was a tie at all.

Suppose one more pair of UK 9 had sold on 31 October, as S-1050. Sizes 8.5 and 9 now both have seven, and =MODE(E2:E19) returns 9, because S-1041 in row 2 is a 9.

Sort the same 18 sales by order number, Z to A, and the web orders come first. W-5561, a UK 8.5, is now in row 2, and the same formula on the same sales returns 8.5.

Same 18 salesFirst rowMODE
Sorted by timeS-1041, UK 99
Sorted by order, Z to AW-5561, UK 8.58.5

The tie check from section 4 catches it once its ranges reach row 19: =COUNTIF(I2:I5,MAX(I2:I5)) reads 2. MODE.MULT goes further and returns every tied value, one per row.

MODE, MODE.SNGL and MODE.MULT. MODE.SNGL is the same function as MODE under its newer name. =MODE.MULT(E2:E19) spills 9 and 8.5 on Microsoft 365; in older versions select two cells and confirm it with Ctrl+Shift+Enter.

Scenario: Add S-1050, Shop, Brown, 9 in row 19 and fill E19. Enter =MODE(E2:E19) and check that it reads 9, then sort A2:E19 by column A, Z to A, and watch it change to 8.5.

Try it in the grid

06Zeros Count, Blanks Don't, No Repeat Is #N/A

Three more inputs change what MODE returns. A blank cell is skipped, but a 0 is a value like any other, so a till that records 0 for a sale keyed without a size can make 0 the most common size.

Exclude the zeros inside the formula:

=MODE(IF(E2:E18>0,E2:E18))

On Microsoft 365 this works as typed. In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter, or IF returns only the first row's result.

The third case is a range where nothing repeats. A new style that sold one pair each of 7, 8 and 9 has no mode, and MODE returns #N/A rather than guessing.

=IFNA(MODE(E2:E18),"No repeat")

Scenario: Enter 9, 0, 8.5, 0, 9 and 0 in K10:K15, and check that =MODE(K10:K15) returns 0. Then enter =MODE(IF(K10:K15>0,K10:K15)) and check that it returns 9.

Try it in the grid

07MODE Cannot Count Words: Use COUNTIF

The buyer also wants the best-selling colour. =MODE(C2:C18) returns #N/A, because the colours are text and MODE sees no numbers at all.

Count each colour against the whole column instead, and take the one with the highest count:

=INDEX(C2:C18,MATCH(MAX(COUNTIF(C2:C18,C2:C18)),COUNTIF(C2:C18,C2:C18),0))

COUNTIF(C2:C18,C2:C18) returns, for every row, how many rows share its colour. MAX finds the highest count, MATCH finds the first row with it, and INDEX returns that row's colour: Brown, with eight pairs.

On Microsoft 365, XLOOKUP says the same thing more directly:

=XLOOKUP(MAX(COUNTIF(C2:C18,C2:C18)),COUNTIF(C2:C18,C2:C18),C2:C18)

Both formulas follow the same tie rule as MODE: the first row in the list wins. Pair them with a colour count table and the tie check from section 4.

In Excel 2019 and earlier, confirm the INDEX version with Ctrl+Shift+Enter.

Scenario: Enter =INDEX(C2:C18,MATCH(MAX(COUNTIF(C2:C18,C2:C18)),COUNTIF(C2:C18,C2:C18),0)) in K5. Check that it reads Brown, and that =COUNTIF(C2:C18,"Brown") reads 8.

Try it in the grid

08Five Traps

  1. Numbers stored as text. MODE skips them without an error. Compare COUNT with COUNTA before trusting the answer.
  2. Trusting the winner alone. A lead of one sale looks the same as a lead of fifty. Keep the count table beside the mode.
  3. Ties decided by sort order. The first tied value in the list wins, so re-sorting the data can change the answer.
  4. Zeros for missing data. A 0 counts as a value and can become the mode. Leave the cell blank or filter the zeros out.
  5. MODE on text. It returns #N/A. Use COUNTIF with INDEX and MATCH for words.

09Four Exercises

  1. Reproduce it. Enter the sales with the web sizes as text and confirm =MODE(D2:D18) returns 9.
  2. Fix it. Convert column D with VALUE and confirm the mode of column E is 8.5.
  3. Measure it. Build the count table and confirm it totals 17 with one size at the top.
  4. Break the tie. Add S-1050, re-sort the list, and watch MODE change while the tie check reads 2.

What to Take Away

Every sale on Brackenfold's list was real, and MODE did exactly what it is built to do. It counted the numbers it could see, and eight of the 17 sizes were text.

Convert before you count, and check COUNT against COUNTA. Then show the counts behind the mode, so the reorder can see a narrow lead or a tie before it buys 12 pairs of the wrong size.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge