Most frequent value between two dates
In this article I will show you how to extract the most frequent value (text or number) between two dates in a column.
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.
Explaining 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.
Download Excel *.xlsx file
Most common value between two dates.xlsx
Related articles
Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if [โฆ]
Count weekday within date range except holidays
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie [โฆ]
Count a specific weekday in a date range
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate [โฆ]
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this [โฆ]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are [โฆ]
Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values [โฆ]
Count unique distinct values in two columns
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 click on cell C12 [โฆ]
Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in [โฆ]
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 [โฆ]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download [โฆ]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when [โฆ]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the [โฆ]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it [โฆ]
2 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.
English
Spanish
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?