Reputation: 31637

Finding SUM except some columns

Below is what I have.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  A  +  B  +  C  +  D  +  E  +  F  +  G  +  H  +  I  +  J  +  K  +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  1  +  2  +  3  +  4  +  5  +  6  +  7  +  8  +  9  +  10 +  50 +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

To find the SUM of this row excluding Cell E I am using formulae as below.

=SUM(A1:D1)+SUM(F1:J1)

NOTE : Cell K have SUM.

Is there any alternate way??

Above is just an example however in my sheet there are around 100 columns and I need to exclude around 10-15 columns.

Upvotes: 2

Answers (4)

andy holaday

Reputation: 2302

If you add a row like this:

Row2: yes yes yes yes no yes yes yes yes yes no ...

(being columns A:K)

Then the answer could be

=SUMIF(2:2,"yes",1:1)

Upvotes: 3

Zeugma

Reputation: 32095

The common approach to address this problem in Excel is to apply the SUM function to the entire range and to substract the exception list out of it, again with the SUM function, this time with a discrete list of individual cells to sum:

=SUM(<range to sum>)-SUM(<exception cell 1>, <exception cell 2>, ...)

This applied to your current example of ~100 cells to sum with say 5 exceptions:

=SUM(A1:CV1)-SUM(E1,M1,Y1,AB1,BU1)

If you want to get your Excel spreadsheet more flexible to those columns exclusion, you may consider to define a named range on the discrete selection of your columns to exclude. If you use it in the substraction SUM formula, you will avoid to change the formula when additional columns need to be excluded : you will only need to change the named range specification.