Forum Discussion
kabeerkhann
Aug 29, 2023Copper Contributor
Excel Formula to sum the above value
Ok this is my dummy data.
There is three column Column A Funded amount, Column B Payment amount, AND Column C is difference amount which i want.
Now there are some conditions and points.
First condition if the funded value is 0 then column c i.e difference value will return 0 so according to the dummy data you can see that Cell A2 is 0 so C2 is also 0
A3 is 0 so C3 is 0. Like that From A2 to A5 Funded amount is 0 so in Difference column from C2 to C5 return 0.
Now second condition or point
in A6 i got 35000 so in cell C6 I want value funded amount i.e A6 - Sum of payment amount ( from B2 to B5 not B6 because B6 will be in sum for next funded value) so according to data 35000 - 204 so in cell C6 i will 34796. This is the first part
Now loop will go forward it will check A7 so according to data a7 is 0 so in c7 it will return 0.
and so on.
Loop will stop at cell A10 because here i got funded amount i.e 15000 now my next condition is i want to sum the payment value from B6 to B9 i'll not include previous value i.e 204 so according to formula it will have to return 15000 - sum of value from B6 to B9 i.e 502 so in cell C10 it will return 15000 - 502 = 14498 and the process will go on.
I am using =IF(A9=0, 0, IF(A9=0, A9-SUM(B$2:B9), A9-SUM(B$2:B9)+B9)) this formula but with this formula i am getting previous values but that i don't want.
Anyone have any idea how to solve this please help me to get rid out of this.
=IF(A2=0,0,A2-SUM(INDEX($B$2:$B$100,IFERROR(LARGE(IF($A$2:A2<>0,ROW($A$2:A2)-1),2),1)):INDEX($B$2:$B$100,ROW()-2)))
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- OliverScheurichGold Contributor
=IF(A2=0,0,A2-SUM(INDEX($B$2:$B$100,IFERROR(LARGE(IF($A$2:A2<>0,ROW($A$2:A2)-1),2),1)):INDEX($B$2:$B$100,ROW()-2)))
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- kabeerkhannCopper ContributorHey Pawn Thanks alottt I got the my answer. But this formula is little bit confusing for me can you please elaborate the working of this formula I really want to know how it works. If you can then please otherwise no worry but thanks for the help sir.