This guide will explain how to find the sum of multiple columns given one or more criteria.

We’ll explain how we can use the SUMIFS and SUMPRODUCT functions to find the total of all values in a given range that follows a certain condition.

##### Table of Contents

Let’s take a look at a quick example where you might need to find the sum of multiple columns while following a certain set of criteria.

Suppose you have a report that keeps track of product sales. Each product has a corresponding category and sub-category. The product is also labeled by how it’s fulfilled. For example, the product may have stock in a local warehouse or be shipped overseas.

The report also further divides the report into which location sold the item. You want to determine the total revenue from all locations for a particular category.

We can use multiple `SUMIF`

or `SUMIFS`

functions to determine the total revenue. The `SUMIFS`

function is helpful because it allows us to calculate the sum of all values in a certain range that meets a particular condition.

We will also explain how we can use the `SUMPRODUCT`

function to create a more mathematical solution. `SUMPRODUCT`

returns the sum of the products of two or more arrays. We can convert our criteria into a one-dimensional array to help filter out values in our sum range.

Now that we have a grasp on when to use the `SUMIFS`

and `SUMPRODUCT`

functions, let’s take a look at how they work on an actual spreadsheet.

**A Real Example of Using SUMIFS on Multiple Columns with One or More Criteria**

The following section provides several examples of how to use this function. We will also go into detail about the formulas and tools used in these examples.

First, let’s take a closer look at our sample dataset.

The table below contains various electronics divided into three categories: Computer Accessories, Computers, and Mobile Devices.

Each product has a corresponding product ID and a field labeled ‘Origin’. The revenues for each product are split into three separate store locations.

**Finding the Sum with a Single Criteria**

In the example below, we used the `SUMPRODUCT`

function to get the total revenue of all computer accessories regardless of branch.

To get the total in cell **C13**, we just need to use the following formula:

=SUMPRODUCT($E$2:$G$10 * ($A$2:$A$10=B13))

Our `SUMPRODUCT`

function finds the product of the range **E2:G10** and the one-dimensional array **A2:A10=B13**. The latter range is an array with TRUE and FALSE values. The values in the range **E2:G10** that are in the same row as these FALSE values are then converted to 0.

We can also make an equivalent formula that uses multiple `SUMIF`

functions instead:

=SUMIF($A$2:$A$10;B13;$E$2:$E$10) + SUMIF($A$2:$A$10;B13;$F$2:$F$10) + SUMIF($A$2:$A$10;B13;$G$2:$G$10)

Finding the Sum with Multiple Criteria

We will have to modify our formula slightly if we need to consider more criteria. For example, suppose we also want to add the condition that the product should come from overseas.

Our `SUMPRODUCT`

formula will need another range to multiply:

=SUMPRODUCT($E$2:$G$10*($A$2:$A$10=B18)*($D$2:$D$10=C18))

Our `SUMIF`

function from earlier will have to be converted into multiple SUMIFS instead:

=SUM(SUMIFS($E$2:$E$10;$A$2:$A$10;B18;$D$2:$D$10;C18);SUMIFS($F$2:$F$10;$A$2:$A$10;B18;$D$2:$D$10;C18);SUMIFS($G$2:$G$10;$A$2:$A$10;B18;$D$2:$D$10;C18))

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.

Are you ready to try out the `SUMIFS`

and `SUMPRODUCT`

functions to get the total of multiple columns? Head over to the next section to read our step-by-step breakdown of how to do it!

**How to Use SUMIFS on Multiple Columns with One or Several Criteria in Excel**

This section will guide you through each step needed to start computing the sum of multiple columns given one or more conditions. You’ll also learn how we can use the `SUMIF`

, `SUMIFS`

, and `SUMPRODUCT`

functions to apply a conditional sum of a range.

- First, let’s use the
`SUMIF`

function to get all branches’ total revenue from computer accessories. Select the cell where you want to place the`SUMIF`

formula. In this example, we’ll use cell**C12**.

- Next, we’ll fill out the arguments needed for the first
`SUMIF`

function. In this example, we’ll start by using`SUMIF`

on the values under Branch 1.

- Use the
`SUMIF`

function to get the total of the remaining columns. We’ll use the ‘+’ operation to add the results of each of these functions. - Hit the
**Enter key**to evaluate the function. If you want to use the**Fill Handle**tool, you must first convert the cell references to absolute references.

- Next, we’ll show you how to use the
`SUMPRODUCT`

function to find the total of multiple columns. Select a cell and type “=SUMPRODUCT(“ to start the function.

- As an argument for
`SUMPRODUCT`

, type the sum range and multiply it by the criteria range.

- Hit the
**Enter key**to return the total amount.

- If you have multiple criteria, multiply the sum range by both criteria ranges.

- We can also use the
`SUMIFS`

function to find the total of multiple ranges given two or more criteria.

- Each
`SUMIFS`

function corresponds to two or more criteria and exactly one column. Use the SUM function to add all columns to a final result.

- Use absolute references to make it easier to use the
**Fill Handle**tool on the formula.

This step-by-step guide should provide you with all the information you need to start using `SUMIFS`

and `SUMPRODUCT`

on multiple columns in Excel.

The `SUMPRODUCT`

and `SUMIFS`

functions are just some of the many Excel functions you can use in your spreadsheets. Our website also offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!