Table of Contents
The SUMPRODUCT function in Excel allows users to calculate the sum of values based on both row and column criteria. This function multiplies corresponding values from two or more arrays and then sums the products. By specifying specific criteria for both rows and columns, the function can accurately calculate the desired sum. This feature is particularly useful for analyzing large sets of data and obtaining specific sums based on various criteria. Users can easily input their criteria and the function will quickly calculate the sum, providing a convenient and efficient way to analyze data in Excel.
Excel: Use SUMPRODUCT with Row and Column Criteria
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 B13and 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
Note: You can find the complete documentation for the SUMPRODUCT function .
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use the SUMPRODUCT function in Excel to calculate the sum of values based on both row and column criteria?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-excel-to-calculate-the-sum-of-values-based-on-both-row-and-column-criteria/
stats writer. "How can I use the SUMPRODUCT function in Excel to calculate the sum of values based on both row and column criteria?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-excel-to-calculate-the-sum-of-values-based-on-both-row-and-column-criteria/.
stats writer. "How can I use the SUMPRODUCT function in Excel to calculate the sum of values based on both row and column criteria?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-excel-to-calculate-the-sum-of-values-based-on-both-row-and-column-criteria/.
stats writer (2024) 'How can I use the SUMPRODUCT function in Excel to calculate the sum of values based on both row and column criteria?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-excel-to-calculate-the-sum-of-values-based-on-both-row-and-column-criteria/.
[1] stats writer, "How can I use the SUMPRODUCT function in Excel to calculate the sum of values based on both row and column criteria?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the SUMPRODUCT function in Excel to calculate the sum of values based on both row and column criteria?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.