# Exclude certain cells from SUM total [closed]

Hello all.

Let's say I have 10 cells with different numbers in them- cells A1 to A10. A11 contains the formula =SUM(A1:A10) and thus displays the total of cells A1 to A10.

What methods could I use to exclude cells A5 and A6 from this total (other than using the formula =A1+A2+A3+A4+A7+A8+A9+A10 in cell A11, of course).

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by appreciatethehelp close date 2017-05-08 03:21:17.424447

Sort by » oldest newest most voted

=SUM(A1:A4)+SUM(A7:A10), =SUM(A1:A10)-SUM(A5:A6),
{=SUMIFS(A1:A10;ABS(ROW(A1:A10)-11/2);">"&1/2)} entered for array-evaluation.
I can also imagine even more complicated solutions.

more

That last one really made my head spin. Very, shall we say, "imaginative"

( 2017-04-03 19:24:12 +0200 )edit

Apologizing for the joke. The third formula is actually working, however.
Of course I first imagined enhanced or scaled-up cases of your needs. If you have to exclude now this and then that subset of a range to sum, I would suggest to use one or more columns specifying either including or excluding the elements to sum in any case and then to use the SUMIF or the SUMIFS function or a construct based on SUMPRODUCT for the purpose.

( 2017-04-03 19:35:05 +0200 )edit