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:

- 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*

- 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.

## Leave a Comment