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 […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
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 […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
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.
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"