MacLochlainns Weblog

Debbie asked a question about why a comparison wasn’t working in the Microsoft Excel SUMIF function when the initial array value was empty. This post answers that question.

The SUMIF function takes a lookup range, which anticipates a one column with a lookup value and another column with a value. The SUMIFS function works along the same line but accepts multiple lookups against a series of lookup list columns.

This shows a simple model of the SUMIF function:

The formula in cell G3 is:

=SUMIF(B$3:B$5,"<"&$E$3,C$3:C$5)

If you left off the $ symbol and made the comparison "<"&E3, you could add the sums for any number of values that are less than a value in the same row (or counting index with named ranges). You could also make the inequality symbol a parameter with this modified formula:

=SUMIF(B$3:B$5,$E$5&$E$3,C$3:C$5)

Now, your analysis model lets you choose whether you want to look for the sum greater than or less than a specific number in the lookup range. Named ranges compare index values between the lookup range and comparison range values, which allows them to be in different rows or spreadsheets. The only problem is that the label has an inverted inequality sign. You can fix that by constantly editing the description or by simply replace the text of the label with a dynamic text string, like this:

=CONCATENATE("Comparison """,$E$5,""" Value")

After applying the fix to the label, it looks like:

When the lookup column contains all nulls, the same formula returns a zero, as shown in the illustration.

You'll also get an error flag, unless you unchecked the Flag formulas that refer to empty cells in the error checking options. The IFERROR function can't be used to suppress this type of error.

Why doesn't the error return a null value? That's because an equality operator compares values in the array against a static or dynamic lookup value. While equality operators don't implicit cast values for comparison, they also don't compare null values. Only the ISBLANK function lets you find an empty cell and it can't be used inside a SUMIF function call.