5 Ways To Sum In Excel If Not Blank

Summing values in Excel can be a straightforward process, but things can get tricky when you need to sum only the cells that are not blank. This is a common problem that many Excel users face, and there are several ways to solve it. In this article, we will explore five different methods to sum cells in Excel if they are not blank.

Why Sum Only Non-Blank Cells?

Before we dive into the solutions, let's understand why you might need to sum only non-blank cells in Excel. Suppose you have a dataset with a mix of numbers and blank cells, and you want to calculate the total of only the cells that contain numbers. If you use the standard SUM function, it will return an incorrect result because it treats blank cells as zeros. By summing only non-blank cells, you can get an accurate total.

Method 1: Using the SUMIF Function

One way to sum cells in Excel if they are not blank is to use the SUMIF function. This function sums cells that meet a specific condition, which in this case is "not blank."

The syntax for the SUMIF function is:

SUMIF(range, criteria, [sum_range])

Where:

• range is the range of cells that you want to check for blank values.
• criteria is the condition that you want to apply, which is "not blank" in this case.
• [sum_range] is the range of cells that you want to sum.

Here's an example formula:

=SUMIF(A1:A10, "<>", A1:A10)

In this formula, the range is A1:A10, the criteria is "<>", which means "not blank," and the [sum_range] is also A1:A10.

Method 2: Using the SUMIFS Function

Another way to sum cells in Excel if they are not blank is to use the SUMIFS function. This function is similar to the SUMIF function, but it allows you to apply multiple conditions.

The syntax for the SUMIFS function is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2],...)

Where:

• sum_range is the range of cells that you want to sum.
• criteria_range1 is the range of cells that you want to check for the first condition.
• criteria1 is the first condition, which is "not blank" in this case.
• [criteria_range2] and [criteria2] are optional additional conditions.

Here's an example formula:

=SUMIFS(A1:A10, A1:A10, "<>")

In this formula, the sum_range is A1:A10, the criteria_range1 is also A1:A10, and the criteria1 is "<>", which means "not blank."

Method 3: Using the AGGREGATE Function

The AGGREGATE function is another way to sum cells in Excel if they are not blank. This function is more flexible than the SUMIF and SUMIFS functions because it allows you to ignore errors and blank values.

The syntax for the AGGREGATE function is:

AGGREGATE(function_num, options, array, [k])

Where:

• function_num is the function number that you want to use, which is 9 for the SUM function.
• options is the option number that you want to use, which is 3 to ignore blank values.
• array is the range of cells that you want to sum.
• [k] is an optional argument that specifies the number of values to ignore.

Here's an example formula:

=AGGREGATE(9, 3, A1:A10)

In this formula, the function_num is 9, the options is 3, and the array is A1:A10.

Method 4: Using the SUBTOTAL Function

The SUBTOTAL function is another way to sum cells in Excel if they are not blank. This function is similar to the AGGREGATE function, but it is more flexible because it allows you to specify a range of cells to ignore.

The syntax for the SUBTOTAL function is:

SUBTOTAL(function_num, ref1, [ref2],...)

Where:

• function_num is the function number that you want to use, which is 9 for the SUM function.
• ref1 is the range of cells that you want to sum.
• [ref2] is an optional range of cells to ignore.

Here's an example formula:

=SUBTOTAL(9, A1:A10)

In this formula, the function_num is 9, and the ref1 is A1:A10.

Method 5: Using an Array Formula

Finally, you can use an array formula to sum cells in Excel if they are not blank. An array formula is a formula that performs multiple calculations on a range of cells and returns an array of values.

The syntax for an array formula is:

=SUM(IF(A1:A10<>"", A1:A10))

In this formula, the SUM function sums the values in the range A1:A10, and the IF function checks if the cells are not blank. If a cell is not blank, the IF function returns the value in that cell, and if a cell is blank, the IF function returns an empty string.

Press Ctrl+Shift+Enter to enter the formula as an array formula.

Gallery of Excel Sum If Not Blank Images

Excel Sum If Not Blank Image Gallery

I hope this article has helped you learn how to sum cells in Excel if they are not blank. Whether you use the SUMIF, SUMIFS, AGGREGATE, SUBTOTAL, or array formula method, you can easily sum only the cells that contain values and ignore the blank cells. Do you have any other questions about Excel formulas? Share your thoughts in the comments section below!