Google-sheets – Sum of numerical values excluding formulas in a Google Spreadsheet

google sheetsworksheet-function

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?

Best Answer

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

Related Topic