#### stacey_milesg

##### New Member
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!

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### AlanY

##### Well-known Member
try this, amend ranges to suit

#### stacey_milesg

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

#### AlanY

##### Well-known Member
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

#### stacey_milesg

##### New Member

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

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

#### AlanY

##### Well-known Member

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

as a test, try to shortened the ranges as

Code:
``=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

##### New Member
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

##### Well-known Member
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,

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

#### stacey_milesg

##### New Member
Yes, let me see what I can attach on here

Replies
1
Views
36
Replies
2
Views
91
Replies
3
Views
82
Replies
1
Views
70
Replies
3
Views
26