Formulas to Sum Columns based on Adjacent Criteria in Excel!
Tags: excelformula, formula
Published: 2023-04-05 05:57:47

---
title: Formulas to Sum Columns based on Adjacent Criteria in Excel!
published: true
date: 2023-03-30 06:42:30 UTC
tags: ExcelFormula,Formula
canonical_url: https://aawexcel.com/excel-formulas-to-sum-columns-based-on-adjacent-criteria/
---

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](https://aawexcel.com/how-to-use-excel-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](https://www.microsoft.com/en-in/microsoft-365/excel)

![Formula to sum Columns based on criteria](http://aawexcel.com/wp-content/uploads/2023/03/Formula-to-sum-Columns-based-on-criteria.webp)
_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](https://aawexcel.com/how-to-use-excel-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](https://aawexcel.com/how-to-use-excel-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](http://aawexcel.com/wp-content/uploads/2023/03/Input-columns.webp)
_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](http://aawexcel.com/wp-content/uploads/2023/03/Enter-the-formula-7.webp)
_Enter the formula_

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

![Output](http://aawexcel.com/wp-content/uploads/2023/03/Output-7.webp)
_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](http://aawexcel.com/wp-content/uploads/2023/03/Fill-remaining-cells.webp)
_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](http://aawexcel.com/wp-content/uploads/2023/03/Columns-with-Red-and-Grey-Values.webp)
_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](http://aawexcel.com/wp-content/uploads/2023/03/Type-the-formula-6-1024x293.webp)
_Type the formula_

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

![Result](http://aawexcel.com/wp-content/uploads/2023/03/Result-5.webp)
_Result_

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

![Fill remaining cells](http://aawexcel.com/wp-content/uploads/2023/03/Fill-remaining-cells-1.webp)
_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](https://aawexcel.com/)**! and **[Excel Formulas](https://aawexcel.com/excel-formula/)**!

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

<video controls poster="http://aawexcel.com/wp-content/uploads/2023/03/um-Columns-based-on-Adjacent-Criteria.png.webp" src="http://aawexcel.com/wp-content/uploads/2023/03/Formulas-to-sum-columns.webm"></video>
_Formulas to Sum Columns based on Adjacent Criteria in Excel_

**Read Also:**

- [ALT + F4: Shortcut to Close Excel File (Windows & Mac)](https://aawexcel.com/quick-shortcut-to-close-excel-file-excel/)
- [How to Show or Hide Zero Values in Excel?](https://aawexcel.com/how-to-show-or-hide-zero-values-in-excel/)
- [Split Cell Contents by Space/Newline/Comma/Period in Excel](https://aawexcel.com/split-cell-contents-by-space-newline-comma-period-in-excel/)
- [CTRL + F1: Shortcut to Expand or Collapse Ribbon in Excel (Windows & Mac)](https://aawexcel.com/shortcut-keys-to-expand-or-collapse-ribbon-in-excel/)
- [CTRL + P: Shortcut to Print a File in Excel (Windows & Mac)](https://aawexcel.com/print-file-shortcut-in-excel/)