Google Sheets: (a) calculate the sum of every other column in a sheet (b) UPTO the column you're in (c) Simplify Row 14 formula from example

[p_s]

Hi,

Hope y'all can help me out.

1. I need to calculate every other cell in a selection (in this example $D2:$M2)
   
   
2. I need to stop the calculation at the cell we are in. [Detailed in the spreadsheet below]
   
   
3. Row 14: is there an easier way to calculate this? This is what I am doing currently.

I've input it into the XL2BB, but it this query is for Google Sheets.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Qty Already have	Qty Required	Cost/Material	Needed	Remaining	Needed	Remaining	Needed		Needed	Remaining	Needed	Remaining
2	2,00,000	Value of Last Column (since this is cumulative)	$ 0.00	8,898	IF($A$2 - [SUM(of Cell D2, F2, H2,J2,L2,N2) but only uptil Column E]<=0,value of calc, 0)	0	$A$2 - [SUM(of Cell D2, F2, H2,J2,L2,N2) but only uptil Column G]<=0,value of calc, 0)	0	$A$2 - [SUM(of Cell D2, F2, H2,J2,L2,N2) but only uptil Column I]<=0,value of calc, 0)	4,044	$A$2 - [SUM(of Cell D2, F2, H2,J2,L2,N2) but only uptil Column K]<=0,value of calc, 0)	0	$A$2 - [SUM(of Cell D2, F2, H2,J2,L2,N2) but only uptil Column M]<=0,value of calc, 0)
3	10,000	10188	$ 1,915,344.00	9,588	0	0	0	0	0	0	0	0	0
4	1,234	1327	$ 123,012.90	10,146	8,912	4,689	13,601	6,756	20,357	0	20,357	0	20,357
5	12,412	12938	$ 6,805,388.00	2,523	0	1,167	0	1,680	0	0	0	0	0
6		3	$ 8.91	21,25,275	21,25,275	7,52,835	28,78,110	19,99,139	48,77,249	2,03,709	50,80,958	0	50,80,958
7		31	$ 961.00	3,98,955	3,98,955	2,05,902	6,04,857	5,09,880	11,14,737	45,738	11,60,475	0	11,60,475
8		33	$ 1,085.70	1,37,892	1,37,892	66,963	2,04,855	1,68,557	3,73,412	18,552	3,91,964	0	3,91,964
9		129	$ 16,641.00	24,504	24,504	11,085	35,589	26,202	61,791	3,222	65,013	0	65,013
10		1990	$ 3,960,100.00	6,771	6,771	3,162	9,933	7,133	17,066	687	17,753	0	17,753
11		1540	$ 2,371,600.00	0	0	0	0	2,519	2,519	0	2,519	0	2,519
12		4460	$ 19,891,600.00	0	0	0	0	0	0	0	0	0	0
13		0	$ 0.00	0	0	0	0	0	0	0	0	0	0
14				Material Cost:	(SUMPRODUCT(E$2:E$13,$C$2:$C$13))	Material Cost:	(SUMPRODUCT(G$2:G$13,$C$2:$C$13))-$E14	Material Cost:	(SUMPRODUCT(I$2:I$13,$C$2:$C$13))-SUM($E14,$G14)	Material Cost:	(SUMPRODUCT(K$2:K$13,$C$2:$C$13))-SUM($E14,$G14,$I14)	Material Cost:	(SUMPRODUCT(M$2:M$13,$C$2:$C$13))-SUM($E14,$G14,$I14,$K14)
Sheet1

 

[p_s]

I'm assuming there's no way to do this.... ?

 

[p_s]

For Rows: E, G, I, K etc I used the following formula (This is for G2, if it was D2, all the G2s would become D2s)

=SUMPRODUCT(--(MOD(COLUMN($D2:G2)-COLUMN(I6)+1,2)=1),$D2:G2)-$A2

For Row 14, I left E14 as it is

For G14 onwards the formula became a copyable:

= SUMPRODUCT(G$2:G$13,$A$2:$A$13)-SUMPRODUCT(E$2:E$13,$A$2:$A$13))

I also used an IF Statement around both formulas to wrap around is so that if the above formula is <=0 the returned value should be 0.

 

[Fluff]

How about in E2

=MAX($D2-$A2,0)

if G2

=MAX(SUMIFS($D2:F2,$D$1:F$1,"Needed")-$A2,0)

 

[p_s]

How about in E2

=MAX($D2-$A2,0)

if G2

=MAX(SUMIFS($D2:F2,$D$1:F$1,"Needed")-$A2,0)

I had thought of using sumifs, but if users add more columns or decide to change the header it will create problems. As a result, I wanted the formula to stay independent of the header. But I like the idea of using MAX () instead of an IF statement. It's a great suggestion, thanks. And yes, E2 can totally be an independant function. I just made it use the formula listed above for the sake of similarity.

Thank you!

 

[Fluff]

Your formula from post#3 does not give the results you show in post#1, should it be

=MAX(SUMPRODUCT(--(MOD(COLUMN($D2:G2),2)=0),$D2:G2)-$A2,0)

If columns are added, then this formula will fail, whereas the sumifs will still be correct.

 

[p_s]

Your formula from post#3 does not give the results you show in post#1, should it be

=MAX(SUMPRODUCT(--(MOD(COLUMN($D2:G2),2)=0),$D2:G2)-$A2,0)

If columns are added, then this formula will fail, whereas the sumifs will still be correct.

Why will it fail? Columns will be added at the end which will look exactly as these columns... In this formula, we skip 1 column in our sum, we could further change it to skip more than 1 column if need be.

 

[Fluff]

When you said

but if users add more columns or decide to change the header it will create problems

I thought you were talking about new inserting columns into the data, as that is the only way it would affect the formula.

 

[p_s]

Assume the pairs of columns that are the same base color, are for 1 item, and each row are the different parts.

We might have some parts, which is Column A

Column D is the parts need to make the item, and Column E is (Parts need - Parts in stock) and this continues on and on, the 1st item is 1st priority, 2nd is next. and etc. Row 14 is the Cost of the new parts needed for EACH item, not cumulative. (And this contines for F:M).

If we need to add more items to make we will add the same set of columns (parts needed, and cumulative parts need) at the end.