Sometimes you need to sum values in columns based on adjacent criteria in a range, What would you do? We suggest you use the formula with the SUMPRODUCT function. This page will guide you to learn the Excel formulas to sum or subtotal the values in adjacent columns based on certain criteria. Get an official version of ** MS Excel** from the following link:
https://www.microsoft.com/en-in/microsoft-365/excel
Formula to sum Columns based on criteria
General Formula
Here we come up with a simple formula to sum columns based on adjacent criteria.
=SUMPRODUCT(–(range1=criteria),range2)
Syntax Explanations
- SUMPRODUCT – In Excel, this function will help to multiply the corresponding array or range and returns the sum of the product. Read more on the SUMPRODUCT Function.
- Minus Operator (-) – This symbol will help to subtract any two values.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
- Range – It represents the input values given in the worksheet.
- Criteria -It is the condition that helps to sum the values.
How to Apply Formula to Sum Columns based on Adjacent Criteria?
In order to find the sum of columns based on adjacent criteria in Excel, we can use an Excel formula. This formula can make use of the SUMPRODUCT function. As we said earlier, the SUMPRODUCT function returns the sum of the products of the selected range or array.
For instance, we will need to get the sum of columns that are adjacent in the specific range of the worksheet. In the following screenshots, you can see the columns with the values A and B. We need to sum the values in each row in the range based on columns A and B.
Input columns
Use ** the **below-mentioned formula in any cell where you want to get the output result.
=SUMPRODUCT(–($B2:$H2=K$1),$C2:$I2)
Enter the formula
Once you have entered the formula, *click * on the *Enter * button to execute it and get the result.
Output
In order to fill the remaining cells, you just need to drag down the first output cell. It will auto-fill the values, as shown in the below image.
Fill remaining cells
If the above-given scenario is not enough to understand this formula and want to learn this with a simple example, kindly move to the following section.
Alternate Example
Here we come up with a simple example to know how to apply this Excel formula in the spreadsheet.
- In the below screenshot, we give the input range in Column B to Column I where we have provided two colors “ Red and Grey ” with different values.
- In order to find the sum of values in the adjacent columns for both colors Red and Grey.
Columns with Red and Grey Values
- Apply the below-given formula to get the result on your spreadsheet.
=SUMPRODUCT(–($B2:$H2=K$1),$C2:$I2)
Type the formula
- You have to press the Enter button to get the result.
Result
- Now, you can auto-fill the remaining cells by drag-down the first resulting cell in the worksheet.
Fill remaining cells
Verdict
From this tutorial, we guided you to learn the simple formulas used to sum the values in the adjacent columns based on certain criteria in Excel. Hope you like this article. Leave a comment or reply below & let me know what you think! Thank you so much for Reading!! To learn more, check out AawExcel! and Excel Formulas!
Video Tutorial
The following video will help you to know how to apply the Excel formula used to sum values in the adjacent columns based on given criteria.
Formulas to Sum Columns based on Adjacent Criteria in Excel
Read Also:
Top comments (0)