You can use the following syntax to use the SUMPRODUCT function in Excel based on both row and column criteria:
=SUMPRODUCT((C2:F10)*(C1:F1=B13)*(B2:B10=B14))
This particular formula will calculate the sum of values in the range C2:F10 only where the column value of C1:F1 is equal to the value in cell B13 and where the row value of B2:B10 is equal to the value in cell B14.
The following example shows how to use this formula in practice.
Example: Use SUMPRODUCT with Row and Column Criteria
Suppose we have the following dataset in Excel that shows the sales of various products by various employees at some company during four sales quarters:
Suppose we would like to calculate the sum of sales only for Product B during Quarter 3.
We can type the following formula into cell B15 to do so:
=SUMPRODUCT((C2:F10)*(C1:F1=B13)*(B2:B10=B14))
The following screenshot shows how to use this formula in practice:
The formula returns a value of 78.
This represents the sum of the sales only for Product B during Quarter 3.
We can confirm this is correct by manually calculating the sum of sales for each row that contains Product B and each column that contains Quarter 3:
Sum of Sales for Product B During Quarter 3: 25 + 29 + 24 = 78
This matches the value calculated by our SUMPRODUCT formula.
Note: You can find the complete documentation for the SUMPRODUCT function here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use SUMPRODUCT Across Multiple Sheets
Excel: Using SUMPRODUCT Only with Values Greater Than Zero
Excel: How to Use SUBTOTAL with SUMPRODUCT
