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

16-3
=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

 

[Ray Gans]

Thank you, that worked perfectly.

Ray

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