Excel: How to Sum If Not Blank


You can use the following formulas in Excel to sum a range of cells if the value in a corresponding range is not blank:

Formula 1: Sum If Not Blank (One Column)

=SUMIF(A:A, "<>", B:B)

This formula sums the values in column B only where the values in column A are not blank.

Formula 2: Sum If Not Blank (Multiple Columns)

=SUMIFS(C:C, A:A, "<>", B:B, "<>")

This formula sums the values in column C only where the values in column A and B are not blank.

The following examples show how to use each formula in practice.

Example 1: Sum If Not Blank (One Column)

The following screenshot shows how to sum the values in the Points column only where the values in the Team column are not blank:

Excel sum if not blank

The sum of the values in the Points column for the rows where Team is not blank is 138.

We can verify this is correct by manually calculating the sum of the points for the teams that are not blank:

Sum of points: 22 + 17 + 28 + 30 + 12 + 11 + 18 = 138.

This matches the value that we calculated using the formula.

Example 2: Sum If Not Blank (Multiple Columns)

The following screenshot shows how to sum the values in the Points column only where the values in the Conference and Team columns are not blank:

The sum of the values in the Points column for the rows where the Conference and Team columns are not blank is 90.

We can verify this is correct by manually calculating the sum of the points where the conference and team is not blank:

Sum of points: 22 + 17 + 28 + 12 + 11 = 90.

This matches the value that we calculated using the formula.

Additional Resources

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

How to Use SUMIF with OR in Excel
How to Sum If Cells Contain Text in Excel
How to Use Wildcard in SUMIFS Function in Excel

Leave a Reply

Your email address will not be published.