< Go Back

Sum top n values with criteria

The sum top n values with criteria in excel are used to calculate the sum of n values that match specified criteria.

What are Sum top n values with criteria function in Excel?

In excel worksheet, sum top n values function with criteria simply means that we can specify the particular condition and calculate the sum of top n values.

How the Sum top n values with criteria function is used in Excel?

To sum n top values with criteria, excel built-in LARGE function inside the SUMPRODUCT function is used. The range of cells is specified that is further compared with given criteria to calculate top n values. In a further section, detailed working is given with example.

Formula or Syntax

=SUMPRODUCT ( LARGE((range=criteria) * (values), {1,2,3,N}))

Parameters or Arguments of Sum top n values with criteria function

The parameters in the Sum top n values with criteria function is:

  •         Range: It indicates the range of cells that are compared to specified criteria.
  •         Values: It indicates numerical values from which top values are returned.
  •         N: It indicates nth value.

Example

An example of the sum top n values with criteria function is given below. Follow the steps to learn the working of function:

  1. Create a table in excel as shown below. First, the LARGE function will be executed to find the Nth largest value in a given range of cells i.e., C5: C12. With LARGE function, you can also calculate 1st, 2nd, 3rd largest values as entering {1, 2, 3}. Hence, here, the results will return values 12, 12, 10.

Now, to filter the values for Red category, (Color= E5) is criteria. E5 cell which is Red is then compared with color range i.e., B5: B12.

Figure 1. Make a table as an example for Sum top n values with criteria

  1.  Now, the final result will include the values that match the given criteria and then, SUMPRODUCT function will add return all values. Similarly, you can apply for Blue color.

Figure 2.Showing Result for Sum top n values with criteria

Note on Usage of Sum top n values with criteria function

The notes on usage are listed below:

  •         The arguments of SUMPRODUCT must have the same dimensions. Otherwise, the function will return error value #VALUE!
  •         SUMPRODUCT consider non-numeric array values as zeros.
  •         The LARGE function will return error value #NUM! if the array is empty.
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
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar