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

## Jump To

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

**Use **the **below-mentioned formula** in any cell where you want to get the output result.

**=SUMPRODUCT(–($B2:$H2=K$1),$C2:$I2)**

Once you have entered the formula, **click **on the **Enter **button to execute it and get the result.

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.

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.

- Apply the below-given formula to get the result on your spreadsheet.

**=SUMPRODUCT(–($B2:$H2=K$1),$C2:$I2)**

- You have to
**press**the**Enter button**to get the result.

- Now, you can auto-fill the remaining cells by drag-down the first resulting cell in the worksheet.

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

**Read Also:**

- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?