Need help: Difference between sum of values with data in one column with adjacent column

Hi All,

I have two columns A and B as shown below with 12 values(A1-A12 and B1-B12)

Please Login or Register  to view this content.

Now In A14 i need a formula, which will

1. Get the sum of cell values in Column B and Sum of Corresponding cell values in Column A
Column B = 21.0+22.5+23.0 = 66.5
Column A = 20.0+20.0+20.0 = 60.0
2. Check if Sum in Column B is Greater than that of Column A
a)If yes then take the difference --> Lets take this as X = 66.5 - 60.0 = 6.5
b)If it is equal or less then take it as X= 0.
3. Get the sum of all values in Column A -- > Lets take it as Y = 223.0
4. Now the result will be X+Y = 6.5+223.0 = 229.5

Please help me in getting this done.. Let me know if you could not understand my requirement.

Thank you.

Originally Posted by haripopuri

Please Login or Register  to view this content.

Looks like you have hardcoded b1:b3 and A1:A3.. but it should not be the case..

next time someone may enter value in B4(u can enter value till B12)

So if value is there in B4 then you have to sum B1,B2,B3,B4 and then A1-A4 then check which is greater and so on..

so ultimately you need to SUM all available values in column b and corresponding values in Column A..then need to do the other tasks.. pls help

You were supposed to ask exactly what you need in the question above.

Originally Posted by :) Sixthsense :)

Please describe these parts in a clear way.......

a)If yes then take the difference --> Lets take this as X = 66.5 - 60.0 = 6.5
b)If it is equal or less then take it as X= 0.
3. Get the sum of all values in Column A -- > Lets take it as Y = 223.0
4. Now the result will be X+Y = 6.5+223.0 = 229.5

I hope you are clear with the first point which says get the sum of all existing values in column B and then sum of corresponding values in column B.

2. Now 2nd point says that, if sum taken from column B is greater than that of column A then take the difference.
3. Now add all the values in column A.
4. To this value(value from 3rd point) add the value got in 2nd point

I hope u are clear now

Originally Posted by haripopuri

You were supposed to ask exactly what you need in the question above.

Sorry for not clear with my question..

Originally Posted by :) Sixthsense :)

Try this........

Formula:

Please Login or Register  to view this content.

dont know if i am missing something.. it is returning 0.0 for me

Please Login or Register  to view this content.

this part is summing up all the values in column A instead corresponding values!

Originally Posted by :) Sixthsense :)

Oops....... Minor changes in formula, try this...

Formula:

Please Login or Register  to view this content.

Same again.. getting 0.0 as result

Originally Posted by :) Sixthsense :)

Its working fine for me... please attach a sample file for review

Ok sample file i ll attach after 1 or 2 hr time.. my cab driver is waiting for me n need to move.. sorry for the delay and thanks for helping me

Originally Posted by :) Sixthsense :)

But I will not be available in online after a hour time for next 12 hrs.. So I am attaching my working file for your reference

Hey,

Your code is working for me in my test excel file but not in my Project file.. The only difference in my test and project file is, in test file the values in column A and B are entered manually where as in my project file, it is driven by a formula.. does this cause any problem??

The below code worked in my project file so as of now the problem is resolved..

Please Login or Register  to view this content.

Thanks a lot for your help I will come up with more question when in doubt

Thank you,.

Hi,

There is some problem with your formula as well as the formula which i gave...

the problem is:

when a cell value in column B is less than the corresponding value in column A, we need to ignore that and sum up only the other values!

i was wrong in my requirement earlier...

could you please help me in that..?

Thank you.

Originally Posted by :) Sixthsense :)

Please describe the above quote little bit in brief for better understating

Ok.. here goes an example

Please Login or Register  to view this content.

in this example.. you can see that B4 is 18 which is less than A4 which is 20. same for B6 and A6

Since B4<A4 and B6<A6, i need to ignore these values..

now sum all other values in column B.. i,e: B1+B2+B3+b5
sum the corresponding values in column A i,e: A1+A2+A3+A5

take the difference between the above 2 i,e: (B1+B2+B3+B5) - (A1+A2+A3+A5)

Now the final result will be SUM(A1:A12) + (B1+B2+B3+B5) - (A1+A2+A3+A5)

I hope it is clear now..

Originally Posted by :) Sixthsense :)

Try this...........

Formula:

Please Login or Register  to view this content.

i really dont understand why your formula is not working in my porject.. it is working fine in my test file where the result should come as 229 and it is giving 229..

but in my project, same data is giving 63 as result instead of 229.. i guess there will be some issue if the cell values are populated using formula!!

Originally Posted by :) Sixthsense :)

In your project file check whether the values are entered as text

i guess it is stored as numbers only.. between how can i check that

Hey check the sample file which i have uploaded here.. now i have copied values from my project file to here and i could notice that result coming here is not correct.. but the same values if you type it in another sheet and use the same formula it works great..!

Originally Posted by :) Sixthsense :)

Hm... Try this revised formula

Formula:

Please Login or Register  to view this content.

cool.. this is working