Here are two formula options to sum a range of cells, when each cell holds a delimiter (in this example, a hyphen character), and you want to separately sum the cells’ numbers to the left and right sides of the delimiter.

In the picture, the array formula in cell B14 that sums the San Francisco Giants’ hits per at-bat is

`=SUM(LEFT(B5:B13,FIND("-",B5:B13)-1)+0)&"-"&SUM(RIGHT(B5:B13,LEN(B5:B13)-FIND("-",B5:B13))+0)`

Recall, being an array formula, it is committed to the cell with `Ctrl+Shift+Enter`

, not just with `Enter`

.

This formula in cell E14 (not an array formula) sums the Oakland A’s hits per at-bat:

`=SUMPRODUCT((LEFT(E5:E13,FIND("-",E5:E13)-1))+0)&"-"&SUMPRODUCT((MID(E5:E13,FIND("-",E5:E13)+1,10))+0)`

Important to note, before the numbers were entered into ranges B5:B13 and E5:E13, the cells in those ranges were formatted as Text. Otherwise, Excel would have regarded most of those entries as dates.

## Leave a Reply