Forum Discussion

kabeerkhann's avatar
kabeerkhann
Copper Contributor
Aug 29, 2023
Solved

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.

  • kabeerkhann 

    =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.

  • kabeerkhann 

    =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.

    • kabeerkhann's avatar
      kabeerkhann
      Copper Contributor
      Hey 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.

Resources