SUMIFS: Sum Range Across Multiple Columns (6 Easy Methods)

To demonstrate how to sum ranges, we have the following dataset of product sales for different months. Let’s determine the total number of sales for a specific product over the course of the month.

Method 1 – Using SUMIFS Function with Helper Column

Steps:

Add a helper column I as Subtotal.
Use the below formula in cell I6:

=SUM(C6:H6)

Press Enter and then drag the Fill Handle down to the rest of column I.

Insert the following formula in cell C29 and hit Enter:

=SUMIFS(I6:I26,B6:B26,B29)

The total Product Sale number of B29 (cell criteria Bean) will appear.

Method 2 – Using the SUM Function

Steps:

Insert the following formula in cell C29:

=SUM((C6:C26+D6:D26+E6:E26+F6:F26+G6:G26+H6:H26)*(--(B6:B26=B29)))

In the formula, (C6:C26+D6:D26+E6:E26+F6:F26+G6:G26+H6:H26) defines the sum of individual six ranges. Then, (B6:B26=B29) declares the range value to be equal to B29 (Bean).

Press Enter. For earlier versions of Excel, press Ctrl + Shift + Enter.

You can use any name of the product in the B29 cell to count the total product sales.

Method 3 – Utilizing SUMIF Function

Steps:

Add a helper column I (see Method 1)
Type the following formula in cell C29.

=SUMIF(B6:B26,B29,I6:I26)

Press Enter.

Method 4 – Applying Multiple SUMIF Functions

Let’s say we want to sum the product sales in random months such as January, March, and May.

Steps:

Enter this formula in cell C29:

=SUMIF(B6:B26,B29,C6:C26)+SUMIF(B6:B26,B29,E6:E26)+SUMIF(B6:B26,B29,G6:G26)

In the formula, SUMIF(B6:B26,B29,C6:C26); is a sum of product sales for B3 products in the B6:B26 range, passing the value to sum from the C6:C26 range. The other SUMIFS repeat the process for the remaining columns we need.

Press Enter.

Method 5 – Using SUMPRODUCT Function

Since we want the sum of the total sales of a particular product, we can use the product name as a ”text” reference.

Steps:

Use the following formula in cell C29:

=SUMPRODUCT((B6:B26=B29)*(C6:H26))

Inside the formula, (C6:H26); returns the criteria as True or False. (B6:B26=B29)*(C6:H26) multiplies the values with the criteria and outputs True or False. SUMPRODUCT((B6:B26=B29)*(C6:H26)) displays the total sale value.