You can use the following syntax to sum values in Excel based on both column and row criteria:
=SUM(IF(B1:F1="Year 4",IF(A2:A9="Mavs",B2:F9)))
This particular formula calculates the sum of values in the range B2:F9 where:
- The column value in the range B1:F1 is equal to “Year 4”
- The row value in the range A2:A9 is equal to “Mavs”
The following example shows how to use this formula in practice.
Example: How to Sum Based on Column and Row Criteria in Excel
Suppose we have the following dataset in Excel that contains information about points scored by basketball players on various teams during five different years:
Suppose we would like to sum the values for the players on the Mavs team during Year 4 only.
To do so, we can type the following formula into cell H2:
=SUM(IF(B1:F1="Year 4",IF(A2:A9="Mavs",B2:F9)))
The following screenshot shows how to use this formula in practice:
The formula tells us that players on the Mavs team during Year 4 scored a total of 62 points.
We can verify this is correct by manually identifying each of the points values for the Mavs players during Year 4:
We can calculate the sum of these values to be: 24 + 23 + 15 = 62
This matches the value calculated by our formula.
Note: Feel free to change “Mavs” and “Year 4” in the formula to calculate the sum of values based on different column and row criteria.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Sum Based on Values in Another Column
Excel: How to Combine Duplicate Rows and Sum
Excel: How to Sum If Greater Than Zero
