CODEWITHSUNDEEP
excelexcel-formula
Diegoctn
Diegoctn

Reputation: 55

Sum column dynamically

Having A1+C1 and B1+D1 in two cells how can I dynamically set up a formula to catch if some column is added.
Let's say the user adds two columns in the middle. I should have A1+C1+E1 and B1+D1+F1.

I thought it would have been automatic but it is not.

Upvotes: 0

Views: 92

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

If you always add two columns then A1+C1 will always be looking at odd number columns and B1+D1 will always be looking at even numbered columns. 

{=SUM(IF(ISODD(COLUMN($A$1:$D$1)),$A$1:$D$1))}

and 

{=SUM(IF(ISEVEN(COLUMN($A$1:$D$1)),$A$1:$D$1))}

As long as you insert columns between A:D the ranges will extend to accommodate.

Edit:
Based on the comment that row 2 contains codes and row 3 contains the figures to add up for each code then this array formula will work: 

{=SUM(IF($A$2:$J$2="H1",$A$3:$J$3))}

Edit2: and if I wake up you can even use the non-array and built in formula:
=SUMIF($A$2:$J$2,"H1",$A$3:$J$3)

The H1 text can be changed to another code or to a cell reference containing the code to get the sum of values in row 3 for the specified code.

As an array formula it must be entered using Ctrl+Shift+Enter.

Upvotes: 1

Dominique
Dominique

Reputation: 17493

Replace:

=A1+C1

By:

=SUM(A1:C1) - B1

In case you want to check if the column number is divisble by three, you can use following formula:

=IF(MOD(COLUMN(A1);3)=0;A1;0) // I've put the values from 1 to 10 in A1-J1
                              // and I've dragged this formula from A2 to J2, 
                              // the values were 0,0,3,0,0,6,0,0,9,0.

Unfortunately I don't have a simple way to sum those values in one easy formula.

Upvotes: 1

Related Questions