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 ColB1 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.