sum list based on two criteria

[Bill.Carlson]

Excel 2003

Quarter Quarter
Year 1 2
1 10% 20%
2 50% 45%
3 40% 35%

The above is part of a tax depreciation table
I'm trying to develop a worksheet function which would lookup the
quarter and year and return the total % depreciated.

for example, a asset purchased in the second quarter of the year, would
depreciate 65% (20% + 45%) over its first two years of life.

As always, help is much appreciated,

Bill Carlson

 

[Harlan Grove]

Bill.Carlson wrote...
....

Quarter Quarter
Year 1 2
1 10% 20%
2 50% 45%
3 40% 35%
....
for example, a asset purchased in the second quarter of the year, would
depreciate 65% (20% + 45%) over its first two years of life.

If the entire table above were named TBL, you could use the formula

=SUMPRODUCT((INDEX(Tbl,0,1)<=ThruYr)*(INDEX(Tbl,2,0)=PurchaseQtr),Tbl)

where you'd replace ThruYr with the year through which you want to sum
depreciation rates and PurchaseQtr with the quarter in which the
property was purchased.

 

[Bill Carlson]

Wonderful!

Thanks Harlan

Bill