Amateur Excel, please help!

Question

&nbsp;Hey there- I'm an amateur excel user who needs some major help!I'm trying to pull out data from an aging report. Some names are exactly the same.So what I am trying to do is pull out data from an aging report into three, based on last name, and then view the aging buckets for each of those. There are duplicate names with identifying account/invoice numbers in another column to differentiate.My division is into three by last names A-H, I-Q, R-Z..&nbsp;if last name begins with A-H, then add up all balances in the 30 bucket for A-H... then add up all balances in the 30 bucket for last names I-Q, R-Z, etc. I'm looking for formulas for the red text spots below... does this make sense?&nbsp;&nbsp;Act Last Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30&nbsp; &nbsp; &nbsp; &nbsp; 60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 120123 Boyd&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $10124 Angio&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $20125 Crispy&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$50126 Posey&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $30127 Posey&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$40128 Marte&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $10129 Boyd&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$50130 Boyd-A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $50131 Boyd-B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $100132 Zebra&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $100&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 90&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;120A-H&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$60&nbsp; &nbsp; &nbsp; &nbsp; $70&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$150i-Q&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $10&nbsp; &nbsp; &nbsp; &nbsp; $30&nbsp; &nbsp; &nbsp; &nbsp; $40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$0R-Z&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$100&nbsp; &nbsp; &nbsp; $0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$0&nbsp;Thank you in advance!!&nbsp;

hansvogelaar · Answer

analytical_ang&nbsp;
See the attached demo workbook. I used SUMPRODUCT formulas.

sergeibaklan · Answer

analytical_ang&nbsp;
In C17

=SUMPRODUCT(
  (LEFT(lastName)&gt;=LEFT($B17)  )*
  (LEFT(lastName)&lt;=RIGHT($B17) )*
  INDEX(data, 0, MATCH(C$16, headers, 0) ) )
and drag to the right and down

sergeibaklan · Answer

analytical_ang&nbsp;
Oops, practically the same as&nbsp;HansVogelaar&nbsp; suggested

analytical_ang · Answer

Hi Hans, I am unable to download the workbook.. Is there any way you could enter the formulas in the response?

hansvogelaar · Answer

analytical_ang&nbsp;

The formula in C16 is =SUMPRODUCT(C$2:C$11*(LEFT($B$2:$B$11)&lt;="H"))
In C17: =SUMPRODUCT(C$2:C$11*(LEFT($B$2:$B$11)&gt;="I")*(LEFT($B$2:$B$11)&lt;="Q"))
In C18: =SUMPRODUCT(C$2:C$11*(LEFT($B$2:$B$11)&gt;="R"))
These can be filled to the right to column F.

Forum Discussion

Amateur Excel, please help!

6 Replies

Resources