Sum if there is any text in a range
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!
Comments

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 nonblanks cells, then ISBLANK() is what you need.
I hope this helps.
Craig

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


AND that worked like a charm. Thank you Craig!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 202 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!