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).
=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.
That last one really made my head spin. Very, shall we say, “imaginative”
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.