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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### AlanY

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

Book1
ABCDEF
1
2John Smith100250
350
425
525
650
7A N Other200200
8An Other150150
84
Cell Formulas
RangeFormula
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

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

Book1
ABCDEF
1
2
3
4
5
6
7
8
9John Smith100225
1050
1125
1250
13A N Other200325
14125
15An Other150150
84
Cell Formulas
RangeFormula
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

##### 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,

Book1
ABCDEF
1
2
3
4
5
6
7
8
9John Smith100225
1050
1125
1250
13A N Other200325
14125
15An Other150150
16fenchurch 23115100150
1750
18A Other1010
84
Cell Formulas
RangeFormula
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

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,600
Messages
5,838,291
Members
430,537
Latest member
Antonio11

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back