SUM rows with text and numerical values

**dgibney** · 01-12-2015, 08:06 PM

I have cells in non-sequential columns that contain either "-" or a numerical value. I need help writing a formula that will consider all "-" values as zero and yield a sum that reads as "0" when the cells are all "-" entries. I've used:

Please Login or Register  to view this content.

to successfully add up columns that do have some cells with both "-" and numerical values, but my attempts at creating a sum of "0" for those rows that have entirely "-" entries have been futile. Any help is much appreciated.
David

**Richard Buttrey** · 01-12-2015, 08:09 PM

Hi,

I don't see this behaviour and do get a sum of zero in these circumstances. Would you upload the workbook so that we can better understand?

**dgibney** · 01-12-2015, 08:23 PM

Unfortunately I cannot share this file due to proprietary restrictions. One thing that may be hanging me up is that the cells with "-" values are the result of rather complex formulas such as "=IF(AQ15="","-",LEFT(AQ15,SEARCH("-",AQ15)-1)*HLOOKUP(UPPER(AR$5),range_units,MATCH(UPPER(RIGHT(AQ15,LEN(AQ15)-SEARCH("-",AQ15))),labels_units,0),FALSE))".

Is this a relevant issue?

Thanks for your effort. David