How to lookup and sum multiple values based on multple columns

[Ray Gans]

I would like to have one table with the names of people with an amount of
money owed combined with the length of time it has been owed. So something
like:

Name 0-15 16-30 30+
Bill 12.75 0 32.16
Steve 0 5 75.25

And another table with the name of a person an amount of money owed and the
date that it is owed from. So something like:

Name Date Fee
Bill 2/3/04 32.16
Steve 5/4/05 50
Steve 6/28/05 25.25
Steve 7/8/05 5
Bill 7/28/05 8.75
Bill 8/1/05 4

I would like for the first chart to be able to look up the data from the
second chart and add it up and place it in the appropriate row under the
correct range of dates.

Thanks,
Ray

[Jim Thomlinson]

This can be done with 3 sum product formulas like this. Your second chart
with the data to be looked up is called sheet 1 and your aged amount by
person is sheet 2. On sheet 2 your people are in cells A2, A3, ...
Aged < 15 days uses this formula
=SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A2)*((Sheet1!$B$2:$B$100>TODAY()-15)*(Sheet1!$C$2:$C$100)))

16-30
=SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A2)*((Sheet1!$B$2:$B$100<TODAY()-16)*((Sheet1!$B$2:$B$100>TODAY()-30)*(Sheet1!$C$2:$C$100))))

30+
=SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A2)*((Sheet1!$B$2:$B$100<TODAY()-30)*(Sheet1!$C$2:$C$100)))

Here is a link to the sumproduct fromula for your reference...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
HTH...

Jim Thomlinson


"Ray Gans" wrote:

> I would like to have one table with the names of people with an amount of
> money owed combined with the length of time it has been owed. So something
> like:
>
> Name 0-15 16-30 30+
> Bill 12.75 0 32.16
> Steve 0 5 75.25
>
> And another table with the name of a person an amount of money owed and the
> date that it is owed from. So something like:
>
> Name Date Fee
> Bill 2/3/04 32.16
> Steve 5/4/05 50
> Steve 6/28/05 25.25
> Steve 7/8/05 5
> Bill 7/28/05 8.75
> Bill 8/1/05 4
>
> I would like for the first chart to be able to look up the data from the
> second chart and add it up and place it in the appropriate row under the
> correct range of dates.
>
> Thanks,
> Ray

[Ray Gans]

Thank you, that worked perfectly.

Ray

"Jim Thomlinson" wrote:

> This can be done with 3 sum product formulas like this. Your second chart
> with the data to be looked up is called sheet 1 and your aged amount by
> person is sheet 2. On sheet 2 your people are in cells A2, A3, ...
> Aged < 15 days uses this formula
> =SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A2)*((Sheet1!$B$2:$B$100>TODAY()-15)*(Sheet1!$C$2:$C$100)))
>
> 16-30
> =SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A2)*((Sheet1!$B$2:$B$100<TODAY()-16)*((Sheet1!$B$2:$B$100>TODAY()-30)*(Sheet1!$C$2:$C$100))))
>
> 30+
> =SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A2)*((Sheet1!$B$2:$B$100<TODAY()-30)*(Sheet1!$C$2:$C$100)))
>
> Here is a link to the sumproduct fromula for your reference...
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Ray Gans" wrote:
>
> > I would like to have one table with the names of people with an amount of
> > money owed combined with the length of time it has been owed. So something
> > like:
> >
> > Name 0-15 16-30 30+
> > Bill 12.75 0 32.16
> > Steve 0 5 75.25
> >
> > And another table with the name of a person an amount of money owed and the
> > date that it is owed from. So something like:
> >
> > Name Date Fee
> > Bill 2/3/04 32.16
> > Steve 5/4/05 50
> > Steve 6/28/05 25.25
> > Steve 7/8/05 5
> > Bill 7/28/05 8.75
> > Bill 8/1/05 4
> >
> > I would like for the first chart to be able to look up the data from the
> > second chart and add it up and place it in the appropriate row under the
> > correct range of dates.
> >
> > Thanks,
> > Ray