MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel: How-to-use SUMIF

with 2 comments

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:

SUMIF_Drawing1

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)

SUMIF_Drawing3

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:

SUNIF_Drawing3Fixed

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

SUMIF_Drawing2Fixed

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.

Written by maclochlainn

January 13th, 2013 at 5:15 pm

2 Responses to 'Excel: How-to-use SUMIF'

Subscribe to comments with RSS or TrackBack to 'Excel: How-to-use SUMIF'.

  1. Thanks. I didn’t know you could do all that with SUMIF. I did have that empty cell flag unchecked so no error message. When I checked the flag, the error message showed up. I thought the comparison cell contained “” as that was what the formula in it returned, but actually that didn’t happen. I mean the formula works but if its test fails it just doesn’t insert any value so the cell is empty. I think.

    Debbie

    15 Jan 13 at 4:10 pm

  2. Debbie, The SUMIF returns a 0 (zero) when the lookup range is empty. Have you embedded the SUMIF function in another function? Could you post the formula you’re trying to fix?

    maclochlainn

    15 Jan 13 at 8:17 pm

Leave a Reply