# MacLochlainns Weblog

Michael McLaughlin's Technical Blog

## Excel: How-to-use SUMIF

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.

Written by maclochlainn

January 13th, 2013 at 5:15 pm

Tagged with ,

### 2 Responses 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