## Most frequent value between two dates

I will in this article show you how to extract the most frequent value (text or number) between two dates in a column.

#### What's on this webpage

## 1. Most frequent value between two dates

Array formula in D18:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### 1.1 Explaining the formula in cell D18

#### Step 1 - Calculate rank order if sorted

TheÂ COUNTIF functionÂ counts values based on a condition or criteria, the < less than sign makes the COUNTIF calculate a rank number if the list were sorted from A to Z.

COUNTIF($C$3:$C$12,"<"&$C$3:$C$12)

becomes

COUNTIF({"BB"; "CC"; "AA"; "CC"; "AA"; "EE"; "DD"; "EE"; "BB"; "AA"},"<"&{"BB"; "CC"; "AA"; "CC"; "AA"; "EE"; "DD"; "EE"; "BB"; "AA"})

becomes

COUNTIF({"BB"; "CC"; "AA"; "CC"; "AA"; "EE"; "DD"; "EE"; "BB"; "AA"}, {"<BB";"<CC";"<AA";"<CC";"<AA";"<EE";"<DD";"<EE";"<BB";"<AA"})

and returns

{3;5;0;5;0;8;7;8;3;0}.

#### Step 2 - Check which values are in range

TheÂ IF functionÂ returns the rank number numberÂ based on a logical expression. It returns boolean value TRUE if the value is in the date range. If boolean value is FALSE the IF function returns "" (nothing).

IF(($B$3:$B$12<=$C$16)*($B$3:$B$12>=$C$15),COUNTIF($C$3:$C$12,"<"&$C$3:$C$12),"")

becomes

IF({1;0;0;0;1;1;0;1;0;0},COUNTIF($C$3:$C$12,"<"&$C$3:$C$12),"")

becomes

IF({1;0;0;0;1;1;0;1;0;0}, {3; 5; 0; 5; 0; 8; 7; 8; 3; 0},"")

and returns

{3; ""; ""; ""; 0; 8; ""; 8; ""; ""}.

#### Step 3 - Calculate the most frequent number

The MODE.SNGLÂ function returns the most frequent number in a cell range or array.

MODE.SNGL(IF(($B$3:$B$12<=$C$16)*($B$3:$B$12>=$C$15),COUNTIF($C$3:$C$12,"<"&$C$3:$C$12),""))

becomes

MODE.SNGL({3; ""; ""; ""; 0; 8; ""; 8; ""; ""})

and returns 8.

#### Step 4 - Find positionÂ of most frequent number in array

TheÂ MATCH functionÂ finds the relative position of a value in an array or cell range.

MATCH(MODE.SNGL(IF(($B$3:$B$12<=$C$16)*($B$3:$B$12>=$C$15), COUNTIF($C$3:$C$12, "<"&$C$3:$C$12), "")), COUNTIF($C$3:$C$12, "<"&$C$3:$C$12),0)

becomes

MATCH(8, COUNTIF($C$3:$C$12, "<"&$C$3:$C$12),0)

becomes

MATCH(8, {3; 5; 0; 5; 0; 8; 7; 8; 3; 0},0)

and returns 6.

#### Step 5 - Return value

TheÂ INDEX functionÂ returns a value based on row number (and column number if needed)

INDEX($C$3:$C$12, MATCH(MODE.SNGL(IF(($B$3:$B$12<=$C$16)*($B$3:$B$12>=$C$15), COUNTIF($C$3:$C$12, "<"&$C$3:$C$12), "")), COUNTIF($C$3:$C$12, "<"&$C$3:$C$12),0))

becomes

INDEX($C$3:$C$12, 6)

and returns "EE" in cell D18.

**Get Excel *.xlsxÂ file**

Most common value between two dates.xlsx

## 2. Most frequent value between two dates - Autofilter

This example demonstrates how to identify the most repeated value in a filtered data set using the Autofilter feature and two formulas.

Formula in cell B15:

Formula in cell D3:

Copy cell D3 and paste to the cells below.

### 2.1 Explaining formula in cell D3

#### Step 1 - Populate arguments

The SUBTOTAL function returns a subtotal from a list or database, you can choose from a variety ofÂ arguments that determine what you want the function to do.

Function syntax: SUBTOTAL(function_num, ref1, ...)

SUBTOTAL(*function_num*, *ref1*, ...)

becomes

SUBTOTAL(3,C3)

#### Step 2 - Evaluate SUBTOTAL function

SUBTOTAL(3,C3)

becomes

SUBTOTAL(3,"BB")

and returns 1.

#### Step 3 - Check if the value is equal to 1

The equal sign is a logical operator, it compares value to value. The result is a boolean value TRUE or FALSE.

SUBTOTAL(3, C3)=1

becomes

1=1

and returns

TRUE.

### 2.2 Explaining formula in cell B15

#### Step 1 - Check which rows are visible

The equal sign is a logical operator, it compares value to value. The result is a boolean value TRUE or FALSE.

D3:D12=TRUE

{TRUE;0;0;0;TRUE;TRUE;0;0;TRUE;FALSE} = TRUE

and returns

{TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}.

#### Step 2 - Convert text values to unique numbers

The COUNTIF function calculates the number of cells that is equal to a condition.

Function syntax: COUNTIF(range, criteria)

COUNTIF($C$3:$C$12, "<"&$C$3:$C$12)

becomes

COUNTIF({"BB"; "CC"; "AA"; "CC"; "AA"; "EE"; "AA"; "DD"; "EE"; "BB"}, "<"&{"BB"; "CC"; "AA"; "CC"; "AA"; "EE"; "AA"; "DD"; "EE"; "BB"})

and returns

{3; 5; 0; 5; 0; 8; 0; 7; 8; 3}.

#### Step 3 - Replace visible values with a unique number

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(D3:D12=1, COUNTIF($C$3:$C$12, "<"&$C$3:$C$12), "")

becomes

IF(TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}, {3; 5; 0; 5; 0; 8; 0; 7; 8; 3}, "")

and returns

{3; ""; ""; ""; 0; 8; ""; ""; 8; ""}

#### Step 4 - Find the most repeated number

The MODE.SNGL function calculates the most frequent value in an array or range of data.

Function syntax: MODE.SNGL(number1,[number2],...)

MODE.SNGL(IF(D3:D12=1, COUNTIF($C$3:$C$12, "<"&$C$3:$C$12), ""))

becomes

MODE.SNGL({3; ""; ""; ""; 0; 8; ""; ""; 8; ""})

and returns 8.

#### Step 5 - Find the relative position of the most repeated number

The MATCH function returns the relative position of an item in an array that matchesÂ a specified value in a specific order.

Function syntax: MATCH(lookup_value, lookup_array, [match_type])

MATCH(MODE.SNGL(IF(D3:D12=1, COUNTIF($C$3:$C$12, "<"&$C$3:$C$12), "")), COUNTIF($C$3:$C$12, "<"&$C$3:$C$12), 0)

becomes

MATCH(8, {3; 5; 0; 5; 0; 8; 0; 7; 8; 3}, 0)

and returns

6.

#### Step 6 - Get the corresponding value in $C$3:$C$12

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX($C$3:$C$12, MATCH(MODE.SNGL(IF(D3:D12=1, COUNTIF($C$3:$C$12, "<"&$C$3:$C$12), "")), COUNTIF($C$3:$C$12, "<"&$C$3:$C$12), 0))

becomes

INDEX($C$3:$C$12, 6)

becomes

INDEX({"BB"; "CC"; "AA"; "CC"; "AA"; "EE"; "AA"; "DD"; "EE"; "BB"}, 6)

and returns

"EE".

### 2.3 How to enable the Autofilter

How do I enable the autofilter for a given data set (shortcut keys)?

- Select any cell in the data set.
- Press and hold CTRL and Shift keys.
- Press L once.
- Release all keys.

How do I enable the autofilter for a given data set (button on the ribbon)?

- Select any cell in the data set.
- Go to tab "Data".
- Press with left mouse button on the "Filter" button.

How do I know autofilter is enabled?

The column header names have a small button each containing an arrow, see the image above.

### 2.4 Enter the formula in cell D3

Copy cell D3 and paste to the cells below.

Make sure the new column header has the autofilter arrow. If not, disable the autofilter and then enable it again.

### 2.5 How to apply a condition to the Autofilter

- Press with mouse on a button next to a column you want to filter.

- A popup menu appears. Deselect all check boxes except "November".

- Press with left mouse button on "OK" button.

### 2.6 How to know if a data set is filtered - Autofilter?

There are two ways you can see if a data set is filtered, the first one is the button.

The button next to column header name "Date" has changed from an arrow to an icon that tells you the data is filtered.

The second one is the row color, filtered data has blue row numbers.

### 2.7 How to clear a filter - Autofilter?

- Press with mouse on the button next to the column header name you want to clear.

- A popup menu appears. Press with left mouse button on "Clear Filter from "Dates"

Filter for that particular column is now removed.

### Count values category

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

This article describes how to count unique distinct values. What are unique distinct values?Â They are all values but duplicates are […]

### Dates category

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

### Excel categories

### 3 Responses to “Most frequent value between two dates”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

I have just replaced data and add some more records. This formula doesn't work. Is this because of my 2007 version?

David,

I donÂ´t think so, did you enter the formula as an array formula?

How would I do if I also want to show a number of how many times the most common value has been used? Like "EE - 2 times"