How to Sum Up Values with VLOOKUP and Handle Blank Cells in Excel

VLOOKUP is a powerful function in Excel that allows you to look up a value in a table and return a corresponding value from another column. However, sometimes you may encounter blank values in your lookup table, which can cause errors or incorrect results. In this article, we will show you how to use VLOOKUP SUM with blank values in Excel, and explain the basic theory and procedures behind it.

The basic formula for VLOOKUP SUM with blank values is:

=SUM(VLOOKUP(lookup_value,table_array,{col1,col2,...},FALSE))

where:

• lookup_value is the value you want to search for in the first column of the table.
• table_array is the range of cells that contains the lookup table.
• {col1,col2,...} is an array constant that specifies the columns you want to sum up.
• FALSE indicates that you want an exact match for the lookup value.

The formula works by using VLOOKUP to return an array of values from the specified columns, and then using SUM to add them up. However, if any of the values returned by VLOOKUP are blank, they will be treated as zero by SUM, which can affect the accuracy of the result.

To avoid this problem, you can use one of the following approaches:

• Replace the blank values with NA() in the lookup table, which will cause VLOOKUP to return an error instead of zero. Then, use the IFERROR function to ignore the errors and sum up only the valid values.
• Use the SUMIF function instead of SUM, and set the criteria to "<>", which means not equal to blank. This will exclude the blank values from the sum.

Procedures

To illustrate the procedures of VLOOKUP SUM with blank values, let’s use the following example:

Suppose you have a table of sales data for different products and months, as shown below:

You want to calculate the total sales for each product, but some of the cells are blank. How can you use VLOOKUP SUM with blank values to get the correct result?

Approach 1: Replace blank values with NA()

The first approach is to replace the blank values with NA() in the lookup table, which will cause VLOOKUP to return an error instead of zero. Then, use the IFERROR function to ignore the errors and sum up only the valid values.

The formula for this approach is:

=SUM(IFERROR(VLOOKUP(lookup_value,table_array,{col1,col2,...},FALSE),0))

where:

• lookup_value is the product name you want to look up.
• table_array is the range of cells that contains the lookup table with NA() values.
• {col1,col2,...} is an array constant that specifies the columns you want to sum up.
• FALSE indicates that you want an exact match for the lookup value.
• IFERROR function returns 0 if VLOOKUP returns an error, or the value itself if not.

For example, to calculate the total sales for product A, you can use the following formula:

=SUM(IFERROR(VLOOKUP("A",B2:H5,{2,3,4,5,6,7},FALSE),0))

This will return 1000, which is the correct result.

Approach 2: Use SUMIF function

The second approach is to use the SUMIF function instead of SUM, and set the criteria to "<>", which means not equal to blank. This will exclude the blank values from the sum.

The formula for this approach is:

=SUMIF(VLOOKUP(lookup_value,table_array,{col1,col2,...},FALSE),"<>")

where:

• lookup_value is the product name you want to look up.
• table_array is the range of cells that contains the lookup table.
• {col1,col2,...} is an array constant that specifies the columns you want to sum up.
• FALSE indicates that you want an exact match for the lookup value.
• "<>" is the criteria that means not equal to blank.

For example, to calculate the total sales for product B, you can use the following formula:

=SUMIF(VLOOKUP("B",B2:H5,{2,3,4,5,6,7},FALSE),"<>")

This will also return 1320, which is the correct result.