Sum bottom n values with criteria

If there is a series of numbers and you are interested to find the sum of smallest n (e.g. smallest 5 values) values then this article will help you do this with a generic formula that can be used for any list of numbers.

Provided with a list of numbers and the criteria defined, to get the sum of smallest ‘n’ values corresponding to that specific criteria, the following formula can be used.

Formula

=SUM(SMALL(IF(range1=criteria,range2),{1,2,3,…,N}))

Explanation

Above written general formula uses IF, SMALL and SUM functions in it. The detailed description of these functions is presented below.

How Sum Bottom ‘n’ Values Formula Works

This formula works with the coordination of 3 formulas. All that needs to be done is to go through the list of numbers and filter the smallest numbers from the entries that fulfill the specified criteria.

IF function checks the criteria for the cell under operation. For instance, if the cell meets the criteria then the corresponding value is returned otherwise a FALSE statement is returned.

SMALL function returns the smallest value in a data range or an array. This function requires 2 input parameters. First is the data range or array name, while the second parameter is the number “N” represents the rank of the number from below in the list with respect to its value, e.g. if N is 3, SMALL function will return the 3rd smallest entry from the array. If more than one entries are required then instead of passing a single number N an array as {1,2,3,…} must be provided which will return smallest, 2nd and 3rd lowest numbers.

Finally, the returned smallest 3 numbers from the entries meeting the criteria, are passed to the SUM function which calculates the SUM of values.

Example

Two objects are considered Pen and Book and their corresponding count is given in front of each entry. There are 8 entries for the object array and hence 8 members of count array. The criteria are defined separately and the Sum array contains the sum calculated for bottom 3 values with criteria.

Figure 1. Sample sheet for calculating the sum of bottom 3 values with criteria

For calculating the sum enter the following formula in F3, to calculate for the lowest 3 counts for the Pen object.

=SUM(SMALL(IF(object=criteria,count),{1,2,3}))

Figure 2. Getting the sum of bottom 3 values with criteria

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar