SUMIF for increases in row of data

[gopalgriffith]

Hi, I am trying to come up with a single formula that will sum the increases across a row of data.

For instance:

0	250	100	500	0	0	0	0	0	0
0	100	300	400	0	0	0	0	0	0

So basically:
a) the sum of the first row would be 650 (the 250 increase in column 2 + the 400 increase in column 4)
b) the sum of the second row would be 400 (100 increase in column 2 + the 200 increase in column 3 + the 100 increase in column 4)

I think the formula I need is something along the lines of:

=SUMIF(B1:J1,">"&A1:I1,B1:J1-A1:I1)

but this is giving an error message.

Clearly I could create a second table with the increments and then sum those - but was hoping to do it with a single formula.

Any thoughts where I am going wrong / how to achieve this in a single step/formula...
Gopes

 

[Special-K99]

This seems to work though I'm not entirely sure how I got to this.

=SUMPRODUCT(((B1:J1)-(A1:I1))*(B1:J1>A1:I1))

Returns the differences between each column
(B1:J1)-(A1:I1)
multiplying them by a 1 or a 0 depending if the column is greater than the previous column
(B1:J1>A1:I1)

 

[awoohaw]

Try this:

this does the difference of every two column. And it also SUM all - including the decreases, if any.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4	0	250	100	500	0	0	0	0	0	0		650
5	0	100	300	400	0	0	0	0	0	0		200
6
Sheet1

Cell Formulas
Range	Formula
L4:L5	L4	=SUM(IF(ISEVEN(COLUMN(B4:K4)),(B4:K4),0) -IF(ISODD(COLUMN(A4:J4)),(A4:J4),0))

 

[Special-K99]

Try this:

this does the difference of every two column. And it also SUM all - including the decreases, if any.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4	0	250	100	500	0	0	0	0	0	0		650
5	0	100	300	400	0	0	0	0	0	0		200
6
Sheet1

Cell Formulas
Range	Formula
L4:L5	L4	=SUM(IF(ISEVEN(COLUMN(B4:K4)),(B4:K4),0) -IF(ISODD(COLUMN(A4:J4)),(A4:J4),0))

Why is the value in L5 200 ?
OP says the value should be 400.

 

[awoohaw]

Why is the value in L5 200 ?
OP says the value should be 400.

then I read the post incorrectly.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4	0	250	100	500	0	0	0	0	0	0		650
5	0	100	300	400	0	0	0	0	0	0		400
Sheet1

Cell Formulas
Range	Formula
L4:L5	L4	=LET(b,DROP(A4:I4,,1),a,DROP(A4:I4,,-1),c,b-a,SUM(IF(c>0,c,0)))

 

[gopalgriffith]

This seems to work though I'm not entirely sure how I got to this.

=SUMPRODUCT(((B1:J1)-(A1:I1))*(B1:J1>A1:I1))

Returns the differences between each column
(B1:J1)-(A1:I1)
multiplying them by a 1 or a 0 depending if the column is greater than the previous column
(B1:J1>A1:I1)

Thanks - this worked perfectly.
Regards
Gopes