Try this where column B contains the values to sum and column A contains the cells that are text:
If you want it to be text and not numeric either then try:
Confirmed with control+shift+enter and not just enter.
Change column references as necessary. Hope that helps.