How to Sum Varying Ranges in Excel Using Non-Empty Cells as Criteria

The basic idea is to use the SUM function to add up the values in a range of cells, but only include the cells that meet certain criteria. For example, if you want to sum the values in column A, but only if the corresponding cells in column B are not empty, you can use a formula like this:

=SUM(IF(B2:B10<>"",A2:A10))

This formula works by using the IF function to check if each cell in B2:B10 is not equal to an empty string (""). If it is not, then it returns the corresponding value in A2:A10. If it is, then it returns FALSE. The SUM function then ignores the FALSE values and adds up the rest.

However, this formula is an array formula, which means you have to enter it with Ctrl+Shift+Enter instead of just Enter. Otherwise, it will only return the value of the first cell in the range.

Procedures

To use this formula, follow these steps:

• Select the cell where you want to enter the formula, such as C2.
• Type =SUM(IF( and then select the range of cells in column B that you want to check, such as B2:B10.
• Type <>"" to indicate that you want to exclude the empty cells.
• Type a comma (,) and then select the range of cells in column A that you want to sum, such as A2:A10.
• Type a closing parenthesis () and then press Ctrl+Shift+Enter to enter the formula as an array formula.
• You should see the sum of the values in column A that have a non-empty value in column B.

Comprehensive explanation

The SUM function is one of the most basic and useful functions in Excel. It allows you to add up the values in a range of cells, such as =SUM(A2:A10). However, sometimes you may want to sum only the values that meet certain criteria, such as having a specific text, number, or date. For example, you may want to sum the sales of a certain product, or the expenses of a certain month.

To do this, you can use the SUMIF or SUMIFS functions, which let you specify one or more criteria for the cells you want to sum. For example, you can use =SUMIF(B2:B10,"Apple",A2:A10) to sum the values in A2:A10 if the corresponding cells in B2:B10 contain the text “Apple”. Or you can use =SUMIFS(A2:A10,B2:B10,"Apple",C2:C10,">=100") to sum the values in A2:A10 if the corresponding cells in B2:B10 contain the text “Apple” and the corresponding cells in C2:C10 are greater than or equal to 100.

However, these functions only work with specific criteria, such as text, numbers, or dates. They do not work with more general criteria, such as non-empty cells, blank cells, or cells that contain formulas. For example, you cannot use =SUMIF(B2:B10,"<>",A2:A10) to sum the values in A2:A10 if the corresponding cells in B2:B10 are not empty. This is because the SUMIF function treats the "<>" as a text value, not as a logical operator.

To overcome this limitation, you can use an array formula that combines the SUM and IF functions. The IF function allows you to test a condition and return one value if it is true, and another value if it is false. For example, you can use =IF(B2<>"",A2,0) to return the value in A2 if B2 is not empty, and 0 if B2 is empty. By using this function for each cell in the range, you can create an array of values that match your criteria. For example, you can use =IF(B2:B10<>"",A2:A10,0) to create an array like this:

Then, you can use the SUM function to add up the values in this array, such as =SUM(IF(B2:B10<>"",A2:A10,0)). This will return the sum of 10, 30, 50, 70, and 90, which is 250.

However, this formula is an array formula, which means it performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are more powerful than regular formulas, but they also have some special rules. One of them is that you have to enter them with Ctrl+Shift+Enter instead of just Enter. This tells Excel that you are entering an array formula, and not a regular formula. If you enter an array formula with just Enter, it will only return the value of the first cell in the array, which is not what you want.

Another rule is that you cannot edit or delete part of an array formula. You have to select the entire formula and then edit or delete it. If you try to edit or delete part of an array formula, you will get an error message.

Scenario

To illustrate how this formula works, let’s use a simple example. Suppose you have a table of sales data for different products and months, like this:

You want to calculate the total sales for each product, but only for the months that have data. For example, for product A, you want to sum the values in Jan, Feb, Mar, May, and Jun, but not Apr, because it is empty. You can use the following formula to do this:

=SUM(IF(B2:G2<>"",B2:G2))

This formula will check each cell in B2:G2 and return the corresponding value if it is not empty, and FALSE if it is empty. Then, it will sum up the values and ignore the FALSE values. The result will be 1700, which is the sum of 100, 200, 300, 500, and 600.

You can copy this formula to the other cells in column H to get the total sales for each product, like this:

Remember to enter the formula with Ctrl+Shift+Enter, and not just Enter.

Other approaches

There are other ways to achieve the same result, depending on your preference and situation. Here are some of them:

• You can use the SUMPRODUCT function instead of the SUM function, and avoid using an array formula. The SUMPRODUCT function multiplies the corresponding values in two or more arrays, and then returns the sum of the products. For example, you can use =SUMPRODUCT((B2:G2<>"")*B2:G2) to get the same result as =SUM(IF(B2:G2<>"",B2:G2)). The difference is that the SUMPRODUCT function does not require Ctrl+Shift+Enter, and it can handle logical values such as TRUE and FALSE as 1 and 0. However, the SUMPRODUCT function may be slower than the SUM function, especially if you have large ranges of data.

• You can use the INDIRECT function to create a reference to a range based on a text string. The INDIRECT function returns the reference specified by a text string. For example, you can use =INDIRECT("B2:G2") to return the same reference as B2:G2. You can use this function to create a dynamic range based on the number of non-empty cells in a row. For example, you can use =INDIRECT("B2:"&CHAR(CODE("B")+COUNTA(B2:G2)-1)&"2") to return a reference to the range B2:F2, because there are five non-empty cells in that row. The formula works by using the CHAR and CODE functions to convert the column letter to a number and vice versa, and adding the number of non-empty cells to the starting column. Then, you can use the SUM function to sum up the values in the range.
• You can use the INDEX function to return a reference to a cell or a range of cells within a table or array. The INDEX function returns the value or the reference of the cell at the intersection of a particular row and column, or a range of cells specified by row and column numbers. For example, you can use =INDEX(B2:G2,1,3) to return the value in C2, which is the cell at the first row and third column of the range B2:G2. You can also use =INDEX(B2:G2,1,1):INDEX(B2:G2,1,COUNTA(B2:G2)) to return a reference to the range B2:F2, which is the range from the first row and first column to the first row and the number of non-empty cells of the range B2:G2. Then, you can use the SUM function to sum up the values in the range.