How can I calculate the sum of values in one column based on corresponding values in another column using Excel?

By /

This process involves using the SUMIF function in Excel to calculate the sum of values in one column based on corresponding values in another column. This function allows you to specify criteria for which cells to include in the sum calculation, such as matching values in a specific column. By using this method, you can easily and accurately calculate the total of a specific set of data based on a specific condition, making it a useful tool for data analysis and management.

Excel: Sum Based on Values in Another Column

Often you may want to sum the values in one column in Excel based on the value in another column.

For example, you may want to sum the values in the Points column of the following dataset based on the corresponding value in the Team column:

Fortunately this is easy to do using the UNIQUE and SUMIF functions in Excel.

The following example shows how to do so.

Example: How to Sum Based on Values in Another Column in Excel

Suppose we have the following dataset in Excel that contains information about points scored by basketball players on various teams:

Suppose we would like to sum the values in the Points column based on the values in the Team column.

To do so, we can first create a list of unique values in the Team column by typing the following formula into cell D2:

=UNIQUE(A2:A13)

The following screenshot shows how to use this formula in practice:

We now have a list of unique team names from the original dataset.

Next, type the following formula into cell E2 to calculate the sum of points values for each unique team:

=SUMIF($A$2:$A$13, D2, $B$2:$B$13)

We can then click and drag this formula down to each remaining cell in column E:

Excel sum based on another column

Column E now shows the sum of values in the Points column for each unique team name in the Team column.

For example, we can see that the sum of points values scored for all players on the Mavs team is 92.

We can confirm this is correct by manually calculating the sum of points for each player on the Mavs:

Sum of Points for Mavs: 28 + 25 + 19 + 20 = 92

This matches the value calculated by our formula.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. How can I combine duplicate rows in Excel and sum the corresponding values?
  2. How can I use Excel to calculate the sum of data based on specific column and row criteria?
  3. Excel: Sum Based on Values in Another Column
  4. How do I filter one column in Google Sheets based on values in another column?
  5. Excel: Sum Based on Column and Row Criteria
  6. How can I split a date into its corresponding day, month, and year using Excel?
  7. How can I sum a column in PySpark based on a condition?
  8. How can I count the values in a column in PySpark, while also applying a condition on the column’s values?
  9. How can I use the fillna() function in PySpark to fill missing values from one column with values from another column?
  10. Spark: How to Sum Column Based on a Condition