Tom’s Tutorials For Excel: SUMIF formula examples

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)

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
4 comments on “Tom’s Tutorials For Excel: SUMIF formula examples
  1. 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.

    • Tom Urtis says:

      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.

  2. > 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

  • Facebook
  • Twitter
  • Instagram
  • Linkedin
  • Youtube