Tom’s Tutorials For Excel: SUMIF formula examples
Here are examples of formula constructions with the
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
In the pictured Example 2, the formula is
=SUMIF(A2:A30," > ="&C1,B2:B30)
SUMIF construction examples (not pictured) while we're on the subject:
Sum with numerical criteria known:
=SUMIF(A1:A100," > 500")
Sum while ignoring the
=SUMIF(B2:B30," < > #DIV/0!")
Sum visually populated cells such as holding null strings:
Sorry i did not get this: Sum visually blank cells such as holding null strings:
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
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:
1 Bill 6
2 Jack 8
3 Stan 2
4 Jane 4
5 Lisa 7
This formula returns 27
It’s true that so will this formula:
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
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
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
returns 23 because no visible character is returned in cell A4.
> But, the formula
> 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.