How to Sum Excel Values Using Horizontal and Vertical Criteria
Question details
The user needs to total values in a spreadsheet based on multiple matching conditions that span across both row labels (vertical) and column headers (horizontal).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting and calculating totals from a complex 2D data table across multiple sections, where a specific category must match in the headers and a specific attribute must match in the rows.
- Observed behavior
- The goal is to successfully return the sum of intersecting values that meet both the horizontal and vertical conditions using array-compatible functions.
Ensure your data table is organized with clear column headers and row labels. The ranges you plan to evaluate in the SUMPRODUCT function must perfectly align with the dimensions of your main data range.
Use SUMPRODUCT for 2D Criteria Matching
Use the SUMPRODUCT function to evaluate arrays against multiple conditions and sum the resulting intersections without needing specialized array keystrokes.
The SUMPRODUCT function handles arrays natively, making it perfect for matching a horizontal range (like column headers) and a vertical range (like row labels).
By multiplying the boolean arrays (TRUE/FALSE) of your criteria against the actual data range, the spreadsheet extracts and sums only the values where both conditions intersect and evaluate to TRUE.
Locate your main data range (e.g., $H$3:$P$7), the horizontal header range (e.g., $H$2:$P$2), and the vertical criteria range (e.g., $Q$3:$Q$7).
In your target result cell, input the formula syntax: =SUMPRODUCT(Data_Range, (Header_Range=Criteria1) * (Row_Range=Criteria2)). For example, use =SUMPRODUCT($H$3:$P$7,($H$2:$P$2=$A3)*($Q$3:$Q$7=B$2)).
Ensure absolute references (using the $ symbol) are applied to your data and criteria ranges so they do not shift when copied. Use mixed references (like $A3 or B$2) for your target criteria cells so they adapt properly when filled down or across.
Click the target cell, grab the fill handle in the bottom-right corner, and drag it across the columns and down the rows to apply the calculation to your entire summary table.
Easily Sum Values with Multiple Criteria in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like SUMPRODUCT, allowing you to quickly process complex multi-condition calculations just like you would in Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your 2D data matrix.
- 2. Select the target cell: Click on the cell in your summary table where you want the calculated total to appear.
- 3. Input the formula: Type =SUMPRODUCT( and highlight your main data range, followed by your vertical and horizontal criteria equations using the multiplication symbol.
- 4. Calculate and fill: Press Enter to instantly get the intersection sum, then drag the cell corner to fill the rest of your summary table.
Frequently Asked Questions
Why is my SUMPRODUCT formula returning a #VALUE! error?
This usually happens if the array dimensions do not match. Ensure your horizontal header range has the same number of columns as your data range, and your vertical criteria range has the same number of rows. Text strings inside ranges intended for math can also trigger this error.
Can I use SUMIFS instead of SUMPRODUCT for horizontal and vertical criteria?
SUMIFS is primarily designed for 1D vertical columns and cannot natively handle criteria that span across both rows and columns simultaneously. SUMPRODUCT is the standard function for 2D array criteria.
Is SUMPRODUCT case-sensitive when matching text criteria?
No, the standard SUMPRODUCT equality check (e.g., Range="Text") is not case-sensitive. If you need a strict case-sensitive match, you must wrap your criteria check inside the EXACT function, like EXACT(Range, "Text").
How does the multiplication asterisk (*) work in the formula?
The asterisk acts as an AND operator for arrays. It converts TRUE/FALSE logical results into 1s and 0s. When both conditions are met (1 * 1 = 1), the corresponding value in the data range is multiplied by 1 and included in the final sum.
