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

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
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Mar 18, 2019
Messages
14
Hi Alan, thank you for the formula, i must be inputting it wrong as it didn’t work as expected.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Mar 18, 2019
Messages
14

ADVERTISEMENT

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.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 18, 2019
Messages
14
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
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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?
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top