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