Sum if there is any text in a range

tedf

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! 

J. Craig Williams

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

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

Try NOT(ISBLANK(@cell))

This will count non-blanks.

Craig

tedf

AND that worked like a charm.  Thank you Craig!