Ask Your Question

Exclude certain cells from SUM total [closed]

asked 2017-04-03 19:05:13 +0200

appreciatethehelp gravatar image

updated 2020-08-07 22:51:48 +0200

Alex Kemp gravatar image

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 flag offensive 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

1 Answer

Sort by » oldest newest most voted

answered 2017-04-03 19:13:59 +0200

Lupp gravatar image

updated 2017-04-03 19:14:58 +0200

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

edit flag offensive delete link more


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

appreciatethehelp gravatar imageappreciatethehelp ( 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.

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

Question Tools

1 follower


Asked: 2017-04-03 19:05:13 +0200

Seen: 2,360 times

Last updated: Apr 03 '17