May 17 2022 11:52 AM
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..
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?
Act Last Name 30 60 90 120
123 Boyd $10
124 Angio $20
125 Crispy $50
126 Posey $30
127 Posey $40
128 Marte $10
129 Boyd $50
130 Boyd-A $50
131 Boyd-B $100
132 Zebra $100
30 60 90 120
A-H $60 $70 $0 $150
i-Q $10 $30 $40 $0
R-Z $0 $100 $0 $0
Thank you in advance!!
May 17 2022 12:08 PM
See the attached demo workbook. I used SUMPRODUCT formulas.
May 17 2022 12:13 PM
In C17
=SUMPRODUCT(
(LEFT(lastName)>=LEFT($B17) )*
(LEFT(lastName)<=RIGHT($B17) )*
INDEX(data, 0, MATCH(C$16, headers, 0) ) )
and drag to the right and down
May 17 2022 12:15 PM
Oops, practically the same as @Hans Vogelaar suggested
May 17 2022 12:19 PM
May 17 2022 12:23 PM
The formula in C16 is =SUMPRODUCT(C$2:C$11*(LEFT($B$2:$B$11)<="H"))
In C17: =SUMPRODUCT(C$2:C$11*(LEFT($B$2:$B$11)>="I")*(LEFT($B$2:$B$11)<="Q"))
In C18: =SUMPRODUCT(C$2:C$11*(LEFT($B$2:$B$11)>="R"))
These can be filled to the right to column F.