DEV Community

loading...

Excel Formulas to Sum the Top N Values with Criteria!!

excelgeek profile image Andrews Originally published at geekexcel.com on ・2 min read

This page will explain the simple formulas used to sum the top N values with certain criteria in Excel Office 365. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Formulas to sum top N values with criteria
Formulas to sum the top N values with criteria

General Formula:

  • Use the below formula to sum the top N values with certain criteria.

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

Syntax Explanations:

  • SUMPRODUCT – In Excel, the SUMPRODUCT Function will help to multiply the corresponding arrays or ranges and returns the sum of the products.
  • LARGE – This function will return the Nth largest value from the given range of data. Read more on the LARGE Function.
  • Range – It is the input range from the worksheet.
  • Criteria – It represents the specific value or criteria that helps to sum the cells.
  • Comma symbol (,) – It is a separator which helps to separate a list of values.
  • Parenthesis () – The main purpose of this symbol is to group the elements.
  • *N * – It is the idea of Nth value.

Example:

  • Let’s consider the below example to sum the top n value with certain criteria.
  • In the below image, we will give the input values in Column B and Column C.
  • After that apply the given formula in the selected area.
  • Finally, it displays the result in the selected cell F3.

Example
Example

Conclusion:

From this tutorial, you can get to know the simple formulas used to sum the values from a range with certain criteria in Excel Office 365. Hope you like this article. If you have any doubts/queries, don’t forget to share it with us. To know further updates keep in touch with our website Geek Excel and Excel Formulas !!

Related Articles:

Discussion

pic
Editor guide