Sum if there is any text in a range

Options
tedf
tedf
edited 12/09/19 in Formulas and Functions

I'd like to make a sum total at the bottom of a sheet in which I add only amounts if the cell in the same row has any text.  I tried using this formula: 

=SUMIF(Location221:Location90, >0.01, [List Price]221:[List Price]90)

, but it only works if there are numbers. 

Any help would be appreciated! 

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    tedf,

    Try this

    =SUMIF(Location90:Location221, ISTEXT(@cell), [List Price]90:[List Price]221)

    Note that I change your range order. While not wrong, I prefer not to tempt Fate.

    ISTEXT return true when TEXT is found. 32 will be false, "32" will be true. 06/03/18 displayed in a Date type column will be false, "Bob" in a Date type column will be true.

    You are, instead looking for non-blanks cells, then ISBLANK() is what you need.

    I hope this helps.

    Craig

  • tedf
    Options

    Craig- This is perfect!  Thank you so much!!!

    I don't want to take advantage of your help here- but if I'd really like for it to work both with text and with a number.  It sounds like ISBLANK(@cell) will find blank cells, but I want it to find the opposite- those with numbers and letters.  

    Ted

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Try NOT(ISBLANK(@cell))

    This will count non-blanks.

    Craig

  • tedf
    Options

    AND that worked like a charm.  Thank you Craig!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!