Calculate Smallest Sales Amount [Microsoft Excel 2019 & Microsoft 365]

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

Syntax
=IF (logical_test, [value_if_true], [value_if_false])
Arguments
  • 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

Arguments
  • 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

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *