Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question

migrated from superuser.com Jun 2 '12 at 14:39

This question came from our site for computer enthusiasts and power users.

add comment

2 Answers 2

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.

share|improve this answer
add comment

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

share|improve this answer
    
My preference would be the /2 option mentioned in the OP, but easy enough to replace M( with BTOTAL(9, in formulae in the relevant column. –  pnuts Jun 17 at 20:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.