Hello, Excellers. Today’s blog post will show you how to find the first and last occurrence of a value. I recently used this method to query the first and last dates that a product appeared in my data set. Or, for what period of time was a product actively selling?. We can quickly achieve this by using the Functions MAXIFS and MINIFS. Both of these are two of the new Functions available in Excel 2016.
Table Of Contents.
The Question To Be Answered!. Data Scenario.
So, let’s suppose my data set looks like this extract below. I have a list of products along with the Week Ending Sales Volumes Data. I want to see the first and last dates that some of my products appeared in the data.
So it is really easy to get the earliest and latest dates for any of our products with the MINIFS and MAXIFS functions.
MINIFS Syntax.
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Where
min_range is a required argument. A range of cells that Excel will use with the specified criteria.
Criteria_range1 this also required and is the set of cells to evaluate with the criteria.
We will use this MINIFS function in our example to find the earliest date one of our products appeared in our sales data. We need to write the following formula to return the relevant value.
MINIFS Explanation.
=MINIFS(B3:B20,C3:C20,F3)
Where min_range is our list of dates, criteria_range1 is our list of products, and F3 contains the product to search. For example, we have correctly calculated that Jamie Doll first appeared in our data Week Ending 26/05/2017. We have correctly calculated that Jamie Doll first appeared in our data Week Ending 26/05/2017.
We can repeat the process of MINIFS with MAXIFS to find out the last week that our product Jamie Doll appeared in our data.
MAXIFS Syntax.
MAXIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Where
max_range this is a required argument and contains the actual range of cells in which the minimum value will be determined
criteria_range1 this also required and is the set of cells to evaluate with the criteria.
criteria1 this is also required and is the criteria in the form of a number, expression, or text that defines which cells will be evaluated as a minimum.
criteria_range2, criteria2 further arguments are optional. These represent additional ranges and their associated criteria. You can enter up to 126 range/criteria pairs.
MINIFS Explanation.
=MAXIFS(B3:B20,C3:C20,F3)
So, max_range is our list of dates, criteria_range1 is our list of products, and F3 contains the product we need to find the last date it appeared. For example, Excel has calculated Jamie Doll last appeared in our data Week Ending 25/06/2017.
That’s how simple it is. How to find the first and last date a product appeared in the sales data.
