SUMIF for increases in row of data

gopalgriffith

New Member
Joined
Feb 21, 2024
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi, I am trying to come up with a single formula that will sum the increases across a row of data.

For instance:
0250100500000000
0100300400000000

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:
Excel Formula:
=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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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)
 
Upvote 0
Solution
Try this:

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

Book1
ABCDEFGHIJKL
1
2
3
40250100500000000650
50100300400000000200
6
Sheet1
Cell Formulas
RangeFormula
L4:L5L4=SUM(IF(ISEVEN(COLUMN(B4:K4)),(B4:K4),0) -IF(ISODD(COLUMN(A4:J4)),(A4:J4),0))
 
Upvote 0
Try this:

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

Book1
ABCDEFGHIJKL
1
2
3
40250100500000000650
50100300400000000200
6
Sheet1
Cell Formulas
RangeFormula
L4:L5L4=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.
 
Upvote 0
Why is the value in L5 200 ?
OP says the value should be 400.
then I read the post incorrectly.
Book1
ABCDEFGHIJKL
1
2
3
40250100500000000650
50100300400000000400
Sheet1
Cell Formulas
RangeFormula
L4:L5L4=LET(b,DROP(A4:I4,,1),a,DROP(A4:I4,,-1),c,b-a,SUM(IF(c>0,c,0)))
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top