I have a long column of numbers in Google Spreadsheet, and occasionally there exists a cell that sums some of the cells below it. For example:

``````=SUM(2:4)
5
10
30
=SUM(6:8)
40
10
10
``````

I'd like to create a grand total of all the numbers in this column excluding these summary cells, which of course double my output. I realize I could just divide the final result by 2, but in the interest of Google Spreadsheet worksheet functions, does anyone have another idea? I tried the equivalent of:

``````=SUMIF(1:8, NOT(ISREF()))
``````

but that just evaluates to false (and thus 0) overall rather than checking each individual cell against the criteria. Is this possible or am I asking too much of my spreadsheet?

If you use SUBTOTAL function in place of your SUM functions then subsequent SUBTOTAL functions will ignore the numbers returned by other SUBTOTAL functions, hence avoiding double counting.

For example if A5 has this formula

`=SUBTOTAL(9,A1:A4)`

[the first argument dictates the function, 9 = SUM]

then if you use this formula in A6

`=SUBTOTAL(9,A1:A5)`

you will see that the two formulas produce the same results because the second one ignores the value returned by the first formula. This works identically in Excel and google spreadsheets

• wow this is incredible. i never knew this feature existed! Jun 6 '19 at 5:07

According to http://productforums.google.com/d/msg/docs/X8DuFNN8GZI/5SpFysHvB4EJ there is no formula that can do this.

If you happen to know something about the characteristics of the numbers in question, e.g. if you know that the sums are always greater than 45 and no individual number is, you could use that, e.g. SUMIF(1:8, ">45")

However, in the same discussion thread there is an example of a custom formula which you could install using the Script Editor. However, offhand it doesn't look like this will work in the SUMIF formula directly because you need to supply as an argument which cell to check, so instead you would have to use another column (which could be hidden) and fill down a cell containing ISFORMULA() and then use that as your criteria range. You might also have to modify the script so that it takes a cell reference rather than row and column offsets. e.g. if and column A contains the values the values you want and column B has ISFORMULA(An) in every cell (where n is the number of cells from the top of that row - fill down will take care of that for you once you've entered the first one). Then your sumif formula would be SUMIF(B1:B8, False, A1:A8).

Hope that makes sense.