New to Excel, please help me!

[stacey_milesg]

Hello,

I am very new to excel and am looking for some help! I’d really appreciate if someone can help me with a formula for one of my cells.

I have a cash book, in column B it has details of the person paying the money in. This money is usually for one client but sometimes it can be for more that one so I have to split the amounts which I do so I’m column E, eg B9 will say ‘John Smith’ and then E9 will have a figure and E10 will have a figure but I leave B10 as a blank field. I need a formula so that F9 will add E9&E10 only when B10 is blank. If it is not blank then it should only pull E9. However it could be that one receipt could be for 5 clients and so I need the formula to add all of those and then work down for the whole spreadsheet.

I’m sorry if this sounds very confusing! It’s hard for me to explain!

Thank you in advance for your help!!

 

[stacey_milesg]

I can do this, but I am concerned that if someone was to pick this task up in my absence they may not and then wouldn’t get the account to balance.

Could I use any other function? As all I need is for F9 to pull the value of E9 or add this with E10 based on the B10 being blank.

Thank you for your help

 

[stacey_milesg]

Hi Alan, no unfortunately not. We usually have over 500 rows with dates but can have more than one payment in on a particular date.

 

[AlanY]

instead of changing the data in Col B, this make use of an extra column that you can put it elsewhere on the sheet.
Copy F9 & G9 down

Book1
	A	B	C	D	E	F	G
8
9		John Smith			100	225	1
10					50		0
11					25		0
12					50		0
13		23114 FENCHURCH REM E29			10000	10100	1
14					100		0
15		A N Other			200	325	1
16					125		0
17		An Other			150	150	1
18		fenchurch 23115			100	150	1
19					50		0
20		A Other			10	10	1
21							0
22							0
23							0
24							0
25							0
26							0
27							0
28		23114 FENCHURCH REM E29			12,643.30	12643.3	2
29							0
30							0
84

Cell Formulas
Range	Formula
F9	=IF(ISBLANK(B9),"",SUMPRODUCT(--(LOOKUP(ROW(B$9:B$30),ROW(B$9:B$30)/(B$9:B$30>0),B$9:B$30&"-"&$G$9:$G$30)=B9&"-"&G9),$E$9:$E$30))
G9	=COUNTIF($B$9:B9,B9)

 

[stacey_milesg]

Fantastic, that’s appears to have worked. Thank you ever so much!

 

[AlanY]

Fantastic, that’s appears to have worked. Thank you ever so much!

this is a better solution as it doesn't need the extra column

Book1
	A	B	C	D	E	F
8
9		John Smith			100	150
10					50
11		23114 FENCHURCH REM E29			10000	10100
12					100
13		A N Other			200	325
14					125
15		An Other			150	150
16		fenchurch 23115			100	150
17					50
18		A Other			10	10
19		23114 FENCHURCH REM E29			12,643.30	12643.3
84

Cell Formulas
Range	Formula
F9	=IF(ISBLANK(B9),"",SUMPRODUCT(--(LOOKUP(ROW(B$9:B$19),ROW(B$9:B$19)/(B$9:B$19>0),B$9:B$19&"-"&ROW(B$9:B$19))=B9&"-"&ROW(B9)),$E$9:$E$19))

 

[stacey_milesg]

That’s great, thank you for all your help.