Summing up columns while you add them

[AdiLonca]

Hello all,

First of all, let me present you my sheet: I have an EXCEL file used to store all received quantities of different products for a project. The first columns contain info such as: designation, supplier, reference. Then:

D = total qty received until now
E = total qty received in the 1st reception

I would like to create a button (Add reception) that creates column F = total qty received in the 2nd reception.
While the macro runs, the cells from column D must change their formula so there would be the sum between E and F.

The button might be used several times, depending of the number of receptions. I am stuck at modifying the formula in column D.

PS: the above is a more simple alternative as one reception contains 4 columns (Qty sent by supplier, Qty received, Difference, Document number) in my file, but still, the problem remains the same, how can I change the formula in column D when I add new receptions?

Thank you,

Adi

 

[mart37]

First you enter in D2 the formula: =SUM(E2:F2)
Then you insert a new column on F.
The formula in D2 is now: SUM(E2:G2)

 

[AdiLonca]

Hi,

Thanks for your reply. It is indeed a good answer for the simple exemple presented by me. But what can I do when my reception had 4 columns.

So:
D = total qty received until now
E = total qty received in the 1st reception
F = document number for the 1st reception

A new reception means 2 more columns:

G = total qty received in the 2nd reception
H = document number for the 2nd reception

And the column D will have to add E and G. And so on: E, G, I, K etc.

 

[mart37]

=SUMPRODUCT(E2:R2*(TRUNC(COLUMN(E1:R1)/2)-1<>COLUMN(E1:R1)/2-1))

You can change in the formula the last column (R) to ?
Or
The same trick as above: in the formula, you refer to 2 additional columns.
Then you insert at the end 2 columns.
then you enter the data.