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

Some videos you may like

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
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this, amend ranges to suit

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">John Smith</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td><td style="text-align: right;background-color: #E2EFDA;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">50</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">25</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">25</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">50</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">A N Other</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">200</td><td style="text-align: right;background-color: #E2EFDA;;">200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">An Other</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">150</td><td style="text-align: right;background-color: #E2EFDA;;">150</td></tr></tbody></table><p style="width:1.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">84</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">B2</font>),"",SUMPRODUCT(<font color="Red">--(<font color="Green">LOOKUP(<font color="Purple">ROW(<font color="Teal">B$2:B$25</font>),ROW(<font color="Teal">B$2:B$25</font>)/(<font color="Teal">B$2:B$25>0</font>),B$2:B$25</font>)=B2</font>),$E$2:$E$25</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">John Smith</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td><td style="text-align: right;background-color: #E2EFDA;;">225</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">50</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">25</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">50</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">A N Other</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">200</td><td style="text-align: right;background-color: #E2EFDA;;">325</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">125</td><td style="text-align: right;background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style=";">An Other</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">150</td><td style="text-align: right;background-color: #E2EFDA;;">150</td></tr></tbody></table><p style="width:1.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">84</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F9</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">B9</font>),"",SUMPRODUCT(<font color="Red">--(<font color="Green">LOOKUP(<font color="Purple">ROW(<font color="Teal">B$9:B$32</font>),ROW(<font color="Teal">B$9:B$32</font>)/(<font color="Teal">B$9:B$32>0</font>),B$9:B$32</font>)=B9</font>),$E$9:$E$32</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">John Smith</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td><td style="text-align: right;background-color: #E2EFDA;;">225</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">50</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">25</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">50</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">A N Other</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">200</td><td style="text-align: right;background-color: #E2EFDA;;">325</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">125</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style=";">An Other</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">150</td><td style="text-align: right;background-color: #E2EFDA;;">150</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style=";">fenchurch 23115</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td><td style="text-align: right;background-color: #E2EFDA;;">150</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">50</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;"></td><td style=";">A Other</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;background-color: #E2EFDA;;">10</td></tr></tbody></table><p style="width:1.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">84</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F9</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">B9</font>),"",SUMPRODUCT(<font color="Red">--(<font color="Green">LOOKUP(<font color="Purple">ROW(<font color="Teal">B$9:B$150</font>),ROW(<font color="Teal">B$9:B$150</font>)/(<font color="Teal">B$9:B$150>0</font>),B$9:B$150</font>)=B9</font>),$E$9:$E$150</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

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

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,431
Members
409,876
Latest member
Akash Yadav
Top