**Tom’s Tutorials For Excel: SUMIF formula examples**

Here are examples of formula constructions with the `SUMIF`

` function.`

```
```When you want to refer to a cell in the formula, enclose the mathematical operators in quotes.

In the pictured Example 1, the formula is `=SUMIF(A2:A30,"="&C1,B2:B30)`

In the pictured Example 2, the formula is `=SUMIF(A2:A30," > ="&C1,B2:B30)`

Other `SUMIF`

construction examples (not pictured) while we're on the subject:

Sum with numerical criteria known: `=SUMIF(A1:A100," > 500")`

Sum while ignoring the `#DIV/0!`

error: `=SUMIF(B2:B30," < > #DIV/0!")`

Sum visually populated cells such as holding null strings: `=SUMIF(A1:A100,"?*",B1:B100)`

Tom:

Sorry i did not get this: Sum visually blank cells such as holding null strings:

=SUMIF(A1:A100,”?*”,B1:B100)

What use is in summing null strings? Can you post an example screenshot?

I tried this formula and have noticed no difference compared to the following formula

=SUMIF(A1:A100,”*”,B1:B100)

They both sum amounts for non-empty criteria field records.

So what role is the question mark playing here? Thanks.

Thanks for your question, and in fact there is a difference, with the key being the word “visually” when I mentioned visually blank cells.

Suppose you have this setup:

`[]ColA ColB`

1 Bill 6

2 Jack 8

3 Stan 2

4 Jane 4

5 Lisa 7

This formula returns 27

=SUMIF(A1:A5,”*”,B1:B5)

It’s true that so will this formula:

=SUMIF(A1:A5,”?*”,B1:B5)

But if those names in column A are returned by conditional formulas with null strings as the negative condition, then the formulas return a different result. Example, if cell A4 contains the formula

=IF(M1=”Jane”,M1,””)

and cell M1 does not contain the name Jane then visually the table looks like cell A4 is empty, when really it holds a null string value. In that case, the questionmark wildcard character, which is a placeholder for a character of some kind, is not satisfied, and therefore tells Excel there is no visual character in cell A4. The formula

=SUMIF(A1:A5,”*”,B1:B5)

will still return 27 because the asterisk wildcard character by itself only cares if something — anything — is in cell A4. In this case the something is a null string formula.

But, the formula

=SUMIF(A1:A5,”?*”,B1:B5)

returns 23 because no visible character is returned in cell A4.

> But, the formula

> =SUMIF(A1:A5,”?*”,B1:B5)

> returns 23 because no visible character is returned in cell A4.

So , it sums everything EXCEPT visual blanks!

I think it is time to correct the wording in your initial post: “Sum visually blank cells such as holding null strings”. Do you agree?

I changed “blank” to “populated”. See if you think that improves the wording.