Tom’s Tutorials For Excel: Summing a Range of Separate Pairs of Delimited Numbers

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.
TTFE0021

Share Button
Posted in Tom's Tutorials for Excel
Tags:
6 comments on “Tom’s Tutorials For Excel: Summing a Range of Separate Pairs of Delimited Numbers
  1. sandeep kothari says:

    Ingenius!

  2. CLE says:

    Hello. Thanks for your tutorial. I tried this formula, and typed it in exactly as you show above, but with normal formulas, when you type in the formula, and hit “enter”, it populates the cell, and you can see the formula above in the fx dialog box, but only the summed outcome populate the cell. In this case however, after I hit ctrl+shift+enter, the entire formula reads out in the cell in text – it stretches out in long form in the cell, and it ALSO displays in the fx dialog box. So I messed up somewhere….

    • Tom Urtis says:

      Check your cell’s format before committing the formula to the cell. Take a close look by selecting the cell, then press Alt+O+E and select Format Cells. On the Number tab, try formatting to General and try the formula again, with Ctrl+Shift+Enter. It should work for you as it did (and seen) in the picture.

  3. Michael Holland says:

    Is there a way to do this, but to also exclude certain lines? Hypothetically if I wanted to sum only the players with 3 or more at bats or to exclude those with zero hits?

    • Tom Urtis says:

      The short answer is yes.

      The longer answer is, it would be an obnoxious formula that would make it worth anyone’s while to rearrange the hits per at-bats in separate cells instead of keeping them in the same cells as this picture shows. I kept it as the latter just to show how it can be done.

      If you are determined to keep the #-# format in the same cells, then the array would need to be expanded to evaluate each side if the dash character for the criteria you mentioned. These days, almost anything you receive from an external source, including what you see in the picture, can be reorganized in separate cells first, which is how I would go about it.

      • Michael Holland says:

        Fair. What if in the Hits Per at bat cell, instead of a #-# format, there was a “DNP” (Did Not Play) for a player on the team for their respective row. Could that be excluded easily?

Leave a Reply

Your email address will not be published.

*