Amateur Excel, please help!

Copper Contributor

 

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

 

6 Replies

@analytical_ang 

See the attached demo workbook. I used SUMPRODUCT formulas.

@analytical_ang 

In C17

image.png

=SUMPRODUCT(
  (LEFT(lastName)>=LEFT($B17)  )*
  (LEFT(lastName)<=RIGHT($B17) )*
  INDEX(data, 0, MATCH(C$16, headers, 0) ) )

and drag to the right and down

@analytical_ang 

Oops, practically the same as @Hans Vogelaar  suggested

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

@analytical_ang 

s.png

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.