# How to Apply SUMIF with Multiple Ranges in Excel

In the sample dataset, we want to get the total price of Apples by adding the price in the January, February, March and Discount columns using the SUMIF function.

### Method 1 – Use of Helper Column to Sum Multiple Ranges

Steps:

• Go to cell H5 and enter the following formula.
`=SUM(D5:G5)`

• We have added all the values in the first row. Use the Fill Handle to auto-fill the rest of the cells.

• We now have the helper column, use the criteria in cell J7 to evaluate the total price for Apple by applying the SUMIFÂ function.

• In cell K7, enter the following formula.
`=SUMIF(B5:B16,\$J\$6,H5:H16)`

This method is only applicable when we can add an additional column.

Read More: SUMIF Across Multiple Sheets in Excel

### Method 2 – Applying the SUM Function to SUMIF with Multiple Ranges

Steps:

• Enter the following formula in cell K6 and press Enter.
`=SUM(SUMIF(B5:B16,J6,D5:D16),SUMIF(B5:B16,J6,E5:E16),SUMIF(B5:B16,J6,F5:F16),SUMIF(B5:B16,J6,G5:G16))`

How Does the Formula Work?

• SUMIF(B5:B16,J6,D5:D16)

This part sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of January (D5:D16).

• SUMIF(B5:B16,J6,E5:E16)

It sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of February (E5:E16).

• SUMIF(B5:B16,J6,F5:F16)

This part sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of March (F5:F16).

• SUMIF(B5:B16,J6,G5:G16)

It sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the Discount column (G5:G16).

• SUM(SUMIF(B5:B16,J6,D5:D16),SUMIF(B5:B16,J6,E5:E16),SUMIF(B5:B16,J6,F5:F16),SUMIF(B5:B16,J6,G5:G16))

It sums up all four results obtained by the above 4 functions.

### Method 3 – SUMIF with Multiple Ranges Using Addition Operator

Steps:

• Enter the following formula in cell K6 and press Enter.
`=SUMIF(B5:B16,J6,D5:D16)+SUMIF(B5:B16,J6,E5:E16)+SUMIF(B5:B16,J6,F5:F16)+SUMIF(B5:B16,J6,G5:G16))`

How Does the Formula Work?

• SUMIF(B5:B16,J6,D5:D16)

It sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of January (D5:D16).

• SUMIF(B5:B16,J6,E5:E16)

This part sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of February (E5:E16).

• SUMIF(B5:B16,J6,F5:F16)

This part sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of March (F5:F16).Â

• SUMIF(B5:B16,J6,G5:G16)

Sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the Discount column (G5:G16).

• SUMIF(B5:B16,J6,D5:D16)+SUMIF(B5:B16,J6,E5:E16)+SUMIF(B5:B16,J6,F5:F16)+SUMIF(B5:B16,J6,G5:G16))

It sums up all four results obtained by the above 4 functions.

## Things to Remember

• Use the 1st method when you can add an additional column.
• Use the 2nd and 3rd methods when you have a small number of ranges to sum.

## Related Articles

<< Go Back to SUMIF Multiple Criteria | Excel SUMIF Function | Excel FunctionsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!