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.

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

<tbody>
</tbody>

 

[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