Function Problems

How to Calculate SUMIF and SUMIFS Horizontally for Multiple Rows in Excel

Jason Miller Oct 15, 2026 873 views

Question details

The user needs to sum values across multiple columns (such as Base Rent and Utilities) for rows that meet a specific text condition.

How to Calculate SUMIF and SUMIFS Horizontally for Multiple Rows in Excel

Product: Spreadsheets
Device & OS: not provided
Scenario: Calculating total costs horizontally across multiple category columns for items that are marked as 'Yes' in a criteria column.
Observed behavior: A standard SUMIF formula only calculates the first specified column and fails to include the additional columns in the total sum.

Before you start

Ensure your data is organized in a structured table format with matching row heights, and verify that your criteria column does not contain hidden characters or trailing spaces.

Solution 1Recommended

Combine Multiple SUMIF Functions

The most straightforward method to sum multiple columns based on a single condition is to calculate a SUMIF for each column and add the results together.

Because the standard SUMIF function maps a single criteria range to a single sum range of the same width, it cannot naturally sum a 2D array (multiple columns). Adding individual SUMIF formulas bypasses this limitation.

Select the target cell

Click on the cell where you want the final calculated total to appear.

Create the first SUMIF formula

Type your first SUMIF formula to calculate the first column (e.g., Base Rent). For example: =SUMIF(A2:A3, "Yes", C2:C3).

Add the second SUMIF formula

Wrap your formulas in a SUM function or simply use a plus sign to add the next column (e.g., Utilities). Your final formula should look like: =SUM(SUMIF(A2:A3, "Yes", C2:C3), SUMIF(A2:A3, "Yes", D2:D3)).

Execute the calculation

Press Enter to calculate the combined multi-column total for rows matching your criteria.

Formatting tip: You can add as many SUMIF parameters into the SUM function as you have columns, making it an easy-to-read solution for a small number of columns.

Solution 2

Use the SUMPRODUCT Function for 2D Ranges

For larger datasets with many columns or complex multi-criteria scenarios (like filtering by date), SUMPRODUCT is a more efficient approach.

Master Spreadsheets with WPS Office

Calculate Complex Array Formulas Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, SUMIF, and SUMPRODUCT functions, allowing you to sum data across multiple rows and columns seamlessly with zero compatibility issues.

1. Open your dataset in WPS: Launch WPS Spreadsheet and open your existing Excel workbook containing the multi-column data.
2. Initiate the formula: Select an empty cell, type the equals sign (=), and begin typing SUM or SUMPRODUCT.
3. Select your ranges: Use your mouse to drag and select the multi-column sum range directly, utilizing WPS's visual array highlights to ensure accuracy.
4. Calculate instantly: Press Enter to execute your horizontal calculation effortlessly.

Fully compatible with Microsoft Excel formulas, functions, and file formats.Free and lightweight spreadsheet tool for everyday data analysis.Intuitive formula autocomplete and syntax highlighting to prevent errors.Cross-platform support for Windows, Mac, Linux, iOS, and Android.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use a standard SUMIFS function to sum multiple columns horizontally?

No, standard SUMIFS requires the sum range and criteria range to be exactly the same size and shape (e.g., one column wide). To sum across a 2D range involving multiple columns and rows, you must use SUMPRODUCT or combine multiple SUMIF functions together.

Why is my combined SUMIF formula returning a #VALUE! error?

This usually happens if there is a typo in your formula syntax or if your ranges are uneven. Ensure that your criteria range (e.g., A2:A3) perfectly matches the row height of your sum ranges (e.g., C2:C3 and D2:D3).

How do I add a specific date condition to my multi-column sum?

You can use the SUMPRODUCT function and multiply arrays for each of your conditions. For example, multiply your text condition array by your date condition arrays (start and end dates), and then multiply that by your multi-column value array: =SUMPRODUCT((A2:A10="Yes")*(B2:B10>=DATE(2020,1,1))*(C2:D10)).

Excel & Data Problems