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 non-blanks 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
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!