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]

try this, amend ranges to suit

Book1
	A	B	C	D	E	F
1
2		John Smith			100	250
3					50
4					25
5					25
6					50
7		A N Other			200	200
8		An Other			150	150
84

Cell Formulas
Range	Formula
F2	=IF(ISBLANK(B2),"",SUMPRODUCT(--(LOOKUP(ROW(B$2:B$25),ROW(B$2:B$25)/(B$2:B$25>0),B$2:B$25)=B2),$E$2:$E$25))

 

[stacey_milesg]

Hi Alan, thank you for the formula, i must be inputting it wrong as it didn’t work as expected.

 

[AlanY]

Hi Alan, thank you for the formula, i must be inputting it wrong as it didn’t work as expected.

the data on post#2 start in Row 2, lets try this instead in F9 and copy down

Book1
	A	B	C	D	E	F
1
2
3
4
5
6
7
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
84

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

 

[stacey_milesg]

Hi Alan, I really appreciate your help but it’s still not working. I’ve tried to input your formula in a blank template likes yours above but I am getting a figure of 289 where as you get the figure 225.

The formula I have input on my actual data is
=IF(ISBLANK(B9),””,SUMPRODUCT(—(LOOKUP(ROW(B$9:B$224),ROW(B$9:B$224)

Thank you.

 

[stacey_milesg]

Sorry I am writing this on my phone so the quotation marks look slightly different!

 

[AlanY]

Sorry I am writing this on my phone so the quotation marks look slightly different!

as a test, try to shortened the ranges as

=IF(ISBLANK(B9),"",SUMPRODUCT([COLOR="#FF0000"]--[/COLOR](LOOKUP(ROW(B$9:B$15),ROW(B$9:B$15)/(B$9:B$15>0),B$9:B$15)=B9),$E$9:$E$15))

note that there are 2 "-" infront of the (LOOKUP(...

 

[stacey_milesg]

Thank you! It has worked! Although I have noticed that some of the data in my column B has numbers as well as text eg cheques come through as the ‘fenchurch 23115’ and so the formula did calculate the total incorrectly. Is there anyway to rectify this rather than having to manually delete the numbers from the text?

 

[AlanY]

Thank you! It has worked! Although I have noticed that some of the data in my column B has numbers as well as text eg cheques come through as the ‘fenchurch 23115’ and so the formula did calculate the total incorrectly. Is there anyway to rectify this rather than having to manually delete the numbers from the text?

it should work,

Book1
	A	B	C	D	E	F
1
2
3
4
5
6
7
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
84

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

have you got an example that it didn't work?

 

[stacey_milesg]

Yes, let me see what I can attach on here