Identify the end of row and SUM total

[Hiten_pan]

Hi all,
I have a set of data in column D which is formulated which pulls the value from different tab with Vlookup function. I need a nested formulae where it can sum up the value automatically at the end of the row as shown in red color.
please note the line items are not fixed so, formulae is dragged after we get sum total. the rows below the sum total should remain blank.


[TABLE="width: 876"]
<tbody>[TR]
[TD]1-SWU00MIE0
[/TD]
[TD]SWU00MIE0
[/TD]
[TD]IRS GBP 1.50000 03/21/18-10Y LCH
[/TD]
[TD] (5,900,000)
[/TD]
[/TR]
[TR]
[TD]1-SWU00JR72
[/TD]
[TD]SWU00JR72
[/TD]
[TD]IRS USD 1.25000 06/21/17-2Y CME
[/TD]
[TD] (6,300,000)
[/TD]
[/TR]
[TR]
[TD]1-SWU00M4A3
[/TD]
[TD]SWU00M4A3
[/TD]
[TD]IRS USD 2.00000 12/20/17-2Y LCH
[/TD]
[TD] (21,400,000)
[/TD]
[/TR]
[TR]
[TD]1-SWU00LBN9
[/TD]
[TD]SWU00LBN9
[/TD]
[TD]IRS JPY 0.41500 03/25/19-10Y LCH
[/TD]
[TD] (60,000,000)
[/TD]
[/TR]
[TR]
[TD]1-SWU00KXL1
[/TD]
[TD]SWU00KXL1
[/TD]
[TD]IRS JPY 0.45000 03/20/19-10Y LCH
[/TD]
[TD] (1,030,000,000)
[/TD]
[/TR]
[TR]
[TD]1-SWU00MO45
[/TD]
[TD]SWU00MO45
[/TD]
[TD]IRS JPY 0.30000 03/20/18-10Y LCH
[/TD]
[TD] (1,033,100,000)
[/TD]
[/TR]
[TR]
[TD]1-US67576GAB32
[/TD]
[TD]US67576GAB32
[/TD]
[TD]ODBRCHT OFFSHRE DRLL FIN
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]1-981CAEII8
[/TD]
[TD]981CAEII8
[/TD]
[TD]CREDIT SUISSE FOB
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]1-22899W925
[/TD]
[TD]22899W925
[/TD]
[TD]CREDIT SUISSE SEC LLC COC
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]1-PRCOFFSET
[/TD]
[TD]PRCOFFSET
[/TD]
[TD]DIFFERENCE BETWEEN WTB & HOLDINGS
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] (662,679,114)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[iggy_]

Do you mean that the number of rows can change? Also what is the formula for the negative value in red, if I add up the values above it I would get a different result.

 

[Hiten_pan]

Do you mean that the number of rows can change? Also what is the formula for the negative value in red, if I add up the values above it I would get a different result.

I have only given you last few rows only because data is huge in numbers and 662k is the result of total sum of the above rows.

 

[iggy_]

Still not clear on the workflow, are you saying as part of the vlookup you wanted to sum up at the end, are you copying formulas to the right, etc? Can you try to explain your issue a bit clearer?

 

[Hiten_pan]

Column A is unique value which is used to pull the data for column b c d using vlookup function. At the end of the row in column D it should sum up.

 

[iggy_]

There is no generic formula that will work if your table size is changing but here are a couple approaches I can think of.
1. Put the sum formula in a different column or summary area. =SUM(D:D) will work even if you have table names and will capture all entries even if you add or delete rows.
2. Resolve it in VBA by finding the last entry and adding all of the cells above it.
3. If the data is consecutive you're only 1 click away from the sum.. "Alt" + "="

Hope that helps

 

[Joyner]

To add to the above, if possible, just put the data in an Excel Table and add a Total Row