Help for an amateur please

[BruceW123]

Dear all,

New to the forum. It's currently 5am and i cant figure this out. I am the treasurer at the sports club. People come in and pay and i note it down. If they are short, i deduct. The spreadsheet i have attached shows various names, the amount that they have paid and how much they are short of the value needed, in this case 40.

I would like to keep a running total of each persons debt. I have used SUMIFs to do it, but i cannot include it in columns E or I, for example but i cant do that as i get the "There are one or more circular references" sign.

What would i would now like to do, is use the column header "difference" and the name in the row, to help calculate the overall balance. For example, Harry shows a -10, a 0 and a -10. I would like his balane column to show -20. Is there a way to do this? Also, the difference column is created automatically. Please help me, and thank you in advance.

 

[Drrellik]

Welcome to the Board.

If you would arrange your data week to week the same it would be very simple to have a running total in each balance column but with you changing the order of names week to week you are making it harder on yourself.

if you would like to actually post some of your data to teh board use the link below XL2BB to install an addone in excel that will let you post your data for other to help

Don

 

[BruceW123]

Hi Don,

You're absolutely right, if I didn't have to change the order it would be easier but this is the nature of it, we will not have the same people every week. I know it's harder this way but can it be done?

 

[Drrellik]

if you added a column in the begining like Access (a key) then you could maybe use a pivot table or help with your sumifs

 

[Peter_SSs]

Does this SUMIFS do what you want? It is copied from D3 down to D7 then D3:D7 is copied and pasted into columns H and L.

23 08 09.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Name	Diff	Pd	Bal	Name	Diff	Pd	Bal	Name	Diff	Pd	Bal
3	Tom	0	40	0	Don	0	40	-20	Ben	-30	10	-90
4	Ken	0	40	0	Bob	0	40	-10	Don	-20	20	-40
5	Bob	-10	30	-10	Ken	-40	0	-40	Bob	-10	30	-20
6	Don	-20	20	-20	Tom	0	40	0	Tom	-30	10	-30
7	Ben	-40	0	-40	Ben	-20	20	-60	Ken	-20	20	-60
Balance

Cell Formulas
Range	Formula
B3:B7,J3:J7,F3:F7	B3	=C3-40
D3:D7,L3:L7,H3:H7	D3	=SUMIFS($B$3:B$7,$A$3:A$7,A3)