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