Summing two running totals

[pedro-egoli]

Following on from earlier query which Barry Houdini answered for me I now want to total the sub totals for Visa and Cash transactions.

Also if possible would like to hide the previous individual sub totals for Visa and Cash transaction once a new sub total is shown.

In the spreasheet below cells in Column G are the ones I want to show the total of sub totals in column H & I.
In this example i want to hide prior results shown in cells H15:H17 and I19 once a new total is shown in G18 and I20, with the same to continue all way down.

Formula was working well until cell G19, so far as totalling the sub totals went but in cell I got a Circular Reference reminder.

Budget actuals trip itinerary etc.xls
	G	H	I	J	K
14	$14,283	Totalofvisa	TotalCash	Visa	Cash
15	-$239	-$239		-$239
16	-$330	-$330		-$91
17	-$577	-$577		-$246
18	-$727	-$727		-$150
19	-$330		-$40		-$40
20			-$190		-$150
BUDGET 2009 TRIP

Assistance once again appreciated.

Pedro

 

[scottylad2]

Excel Workbook
	D	E	F
16
17	BUDGET	5000
18	METHOD	period 1
19	cash	265
20	cash	65
21	cash	86
22	visa	854
23	cash	125
24	cash	365
25	visa	4523
26	visa	3698
27	visa	1256
28	cash	321
29	cash	2563
30
31
32	VISA	161
33	CASH	497
34
Sheet1

This is the data before i run the macro, it can be tidied to suit whatever your layout is and i've run it twice so the totals at the bottom of the first sheet are from last run. If you count the transactions in the box then compare after i run the macro in the second image.....works a treat

Excel Workbook
	D	E
17	BUDGET	5000
18	XXX	XXX
19	cash
20	cash
21	cash
22	visa
23	cash
24	XXX
25	visa
26	XXX
27	visa
28	XXX
29	cash
30
31
32	VISA	10331
33	CASH	3790
34
Sheet1

 

[scottylad2]

What your not seeing, is i had the macro recorder going and wrote a sumif for each category, and got the totals, then i did a copy and paste values only in the totals box before deleting the cash or card, all while the macro was recording and it does what you wanted. Heres the code,

Sub Macro9()
'
' Macro9 Macro
'

'
ActiveCell.FormulaR1C1 = "=SUMIF(R[-13]C[-1]:R[-3]C[1],RC[-1],R[-13]C:R[-3]C)"
Range("E33").Select
ActiveCell.FormulaR1C1 = "=SUMIF(R[-14]C[-1]:R[-4]C[1],RC[-1],R[-14]C:R[-4]C)"
Range("E32:E33").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("E19:E29").Select
Selection.ClearContents
Range("D31").Select
End Sub

but if you don't know how to change it just use the macro recorder while your making up your sheet

hope it helps

 

[pedro-egoli]

Thanks Scotty,
Before I start to try and follow your instructions must mention that in my spreadsheet that relate to individual items (C15:F20) come initially from another sheet and are transferred via formula to either column H or I.
This also refers to Column B (visa or Cash)
In your example you have just entered figures into column K and I am trying to get my head around how to handle your suggestion within the framework of my sheet.

Might be best if I display my full sheet again so you can see what I mean

Budget actuals trip itinerary etc.xls
	B	C	D	E	F	G	H	I	J	K
14		$3,530	$2,553	$6,250	$1,950	$14,283	Totalofvisa	TotalCash	Visa	Cash
15	visa	-$204	-$35			-$239	-$239		-$239
16	visa	-$61	-$30			-$330	-$330		-$91
17	visa	-$204	-$42			-$577	-$577		-$246
18	visa				-$150	-$727	-$727		-$150
19	cash		-$40			-$330		-$40		-$40
20	cash			-$150				-$190		-$150
BUDGET 2009 TRIP (2)

Would also mention my understanding macros is not very good.

Pedro

 

[pedro-egoli]

Finally sorted it. Changed column G to total expenditure from both sources on daily basis and then used Scotty's suggestion.
Of course the total was a simple sum.

Budget actuals trip itinerary etc.xls
	B	C	D	E	F	G	H	I	J
14		$3,530	$2,553	$6,250	$1,950	$14,283	visa	cash	TotalVisaandCash
15	visa	-$204	-$35			$239	$727	$190	$917
16	visa	-$61	-$30			$91
17	visa	-$204	-$42			$246
18	visa				-$150	$150
19	cash		-$40			$40
20	cash			-$150		$150
BUDGET 2009 TRIP

Thanks for the help.

Pedro