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!!

 

[AlanY]

Yes, let me see what I can attach on here

unfortunately you're not allowed to attach files here, you can share them in Google drive etc

 

[stacey_milesg]

Sorry it seems I am unable to get on to the website through my work system so I will have to type the data below.

B26 has the text ‘23114 FENCHURCH REM E29 has the value 12,643.30 and the calculation in F29 is 99,496.64

 

[AlanY]

you must have the ‘23114 FENCHURCH REM E29' else where in Column B, otherwise I can't see where is the 99,496.64 came from.

Book1
	A	B	C	D	E	F
8
9		John Smith			100	225
10					50
11					25
12					50
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
20
21
22
23
24
25
26		23114 FENCHURCH REM E29			12,643.30	12643.3
27
28
84

Cell Formulas
Range	Formula
F26	=IF(ISBLANK(B26),"",SUMPRODUCT(--(LOOKUP(ROW(B$9:B$150),ROW(B$9:B$150)/(B$9:B$150>0),B$9:B$150)=B26),$E$9:$E$150))

 

[AlanY]

could you put =COUNTIF(B:B,B26) on the spreadsheet somewhere except in Col B to see what's the result you get please

 

[stacey_milesg]

Hello Alan, I will do this now for you. Although I may have just realised the problem, the particular values which appear to be incorrect are from the day payees therefore the name is appearing more than once throughout the month.

 

[stacey_milesg]

And the value I had back for that formula was 5

 

[AlanY]

And the value I had back for that formula was 5

that means you have 5 entries of ‘23114 FENCHURCH REM E29' in Col B, the formula should works out the sum of those 5 plus the empty rows that behind.

Is that what you're after?

 

[stacey_milesg]

No, I would still want those to calculate individually as I need the value in column F to be as per my bank line as it was a different receipt on a different date.

 

[AlanY]

No, I would still want those to calculate individually as I need the value in column F to be as per my bank line as it was a different receipt on a different date.

ok, let me have a look of it.
one easy option is to add a subscript of each payment, like this

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

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

 

[AlanY]

No, I would still want those to calculate individually as I need the value in column F to be as per my bank line as it was a different receipt on a different date.

apart from Col B, are there any useful info in other columns, e.g. dates that can help to distinguish them?