New to Excel, please help me!

stacey_milesg

New Member
Joined
Mar 18, 2019
Messages
14
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!!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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))
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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(...
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top