atlas

Tom’s Tutorials For Excel: Summing The First Nth Numbers in a Mixed List

Crazy requests can arise when dealing with crazy data. Here you need to sum the first three numbers that appear in a list that contains numbers, text, and empty cells.

The array formula in pink-colored cell E2 is
=SUM(SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{1,2,3})-1,)))



Recall, array formulas are entered by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not entered by you; Excel places them automatically when the array formula is properly applied.

This entry was posted in Tom's Tutorials for Excel and tagged , , , , , , , , , , , , , , . Bookmark the permalink.

5 Responses to “Tom’s Tutorials For Excel: Summing The First Nth Numbers in a Mixed List”

  1. Chuck Hamdan says:

    Although I followed to the “T” your instructions and have used the Ctrl-Shift-Enter for the array to work but I am getting N/A as a result. If I run separately {1} I get 7 for {2} I get 9 and for {3} I get 5 but when I integrate them under one as {1,2,3} using the double SUM I get N/A. Weird… Either I am missing something or there is something wrong.

    I even copied and pasted your formula and pressed Ctrl-Shift-Enter and same result N/A.

    Regards,
    Chuck

    • Avatar of Tom Urtis Tom Urtis says:

      Thanks for following my blog. The screen shot is taken directly from my spreadsheet so I know the formula works. I cannot think of why you do not get the same result if you are doing it the exact same way I did it. Go ahead and email me a simple workbook with your attempt that does not work, and I’ll see if there is something wrong with my formula, or maybe if there is something wrong with what you did.
      Tom

  2. chrisham says:

    Tom, Got it! So the first sum is basically a wrapper to lay out the array, and the seconds sums it all.. Thanks for your response.

  3. chrisham says:

    Tom thanks for the tutorials…… I do learn a lot from formulas.
    Why do you need to wrap this with 2 Sum function. The Offset lays out the array and the first sum ought to sum this array, but it does not. The second Sum function does the sum.

    • Avatar of Tom Urtis Tom Urtis says:

      Hi, thanks for following my blog posts. Here’s why I used the initial SUM function:

      First, keep in mind there is an array constant for the first, second, and third found number. Therefore, without the first SUM that wraps the formula, it can be broken out like this:

      SUM:

      =SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{1})-1,))
      which returns 7

      plus

      =SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{2})-1,))
      which returns 9

      plus

      =SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{3})-1,))
      which returns 5

      Therefore, summing 7, 9, and 5 equals 21. Is that what you are asking?


Leave a Reply