How to lookup and sum multiple values based on multple columns
 

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

How to lookup and sum multiple values based on multple columns
 

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 formul
=SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A2)*((Shee t1!$B$2:$B$100TODAY()-15)*(Sheet1!$C$2:$C$100)))

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

30
=SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A2)*((Shee t1!$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

How to lookup and sum multiple values based on multple columns
 

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)*((Shee t1!$B$2:$B$100TODAY()-15)*(Sheet1!$C$2:$C$100)))

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

30+
=SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A2)*((Shee t1!$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