Welcome to Excel Jet Consult. In this blog post, we are going to consider how to calculate smallest sales amount in MS Excel 2019 and Microsoft 365. Let’s get started
Video
Sample Data
We have a sample data below which is formatted as an Excel Table. Also, we have drop-down list in cell E1 which houses the unique Payment Method.
MIN & IF Functions
We are going to see how to use the MIN & IF Functions for the single criteria calculation
MIN Function
Returns the smallest number in a set of values.
Syntax
MIN(number1, [number2], …)
The MIN function syntax has the following arguments:
- Number1, number2, … Number1 is optional, subsequent numbers are optional. 1 to 255 numbers for which you want to find the minimum value.
IF function
Checks whether a condition is met and returns one value if TRUE and another value if FLASE
- logical_test – A value or logical expression that can be evaluated as TRUE or FALSE.
- value_if_true – [optional] The value to return when logical_test evaluates to TRUE.
- value_if_false – [optional] The value to return when logical_test evaluates to FALSE.
Calculation
- If cell G1, execute the formula: =MIN(IF(ftSales[Payment Method]=E1,ftSales[Sales Amount],””))
The above formula evaluates whether the value in cell E1 equals to the values in the Payment Method column. The formula returned the corresponding Sales Amount for values that are true and empty for falls. Then, we wrapped MIN function to return the smallest value from what the IF function delivered
- Click Enter
From the caption below, we can see that the smallest value for Skrill Payment Method is ₦19,096
MINIFS functionThe MINIFS function returns the minimum value among cells specified by a given set of conditions or criteria. The MINIFS is strictly available in Microsoft Excel 2019 version and M365
Syntax
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- min_range – Range of values used to determine minimum.
- range1 – The first range to evaluate.
- criteria1 – The criteria to use on range1.
- range2 – [optional] The second range to evaluate.
- criteria2 – [optional] The criteria to use on range2.
Calculation
In cell G2, execute the formula: =MINIFS(ftSales[Sales Amount],ftSales[Payment Method],E1)
Click Enter
From the caption below, we can see that the smallest value for Skrill Payment Method is ₦19,096
MIN & FILTER Functions
The FILTER function allows filtering a range of data based on criteria defined
- array – Range or array to filter.
- include – Boolean array, supplied as criteria.
- if_empty – [optional] Value to return when no results are returned.
Calculation
In cell G3, execute the formula: =MIN(FILTER(ftSales[Sales Amount],ftSales[Payment Method]=E1))
Click Enter
From the caption below, we can see that the smallest value for Skrill Payment Method is ₦19,096
See you in the next tutorial