conditional summing progressively thru a matrix

[HGood]

I have a matrix as follows;

Q1 Q2 Q3 Q4
2002 4.6% 2.6% .2% .5%
2003 2.8% .8% 2.0% 2.4%
2004 0.0% 0.0% 7.9% 0.0%
2005 2.5% .7%
2006
2007
2008
2009
2010
2011
2012

The entire matrix may or may not be filled with percentage of progress, it
depends on the year. Each quarter of each year, another figure will be added
to the matrix. If it was filled, it would total 100% of the project.

I would like to enter into one cell any of the following four quarters: Q1
through Q4 from a dropdown list.
I'd like to enter into a second cell from a drop down list any year from
2002 through 2012.

Based on what Quarter and Year are entered into the two cells, I'd like it
to sum for me the progress of the project from the beginning up until the
Quarter and Year selected. For example, if I entered Q1 and 2005, the
formula would need to sum all across rows 2002, 2003, 2004 to and including
Q1 2005. It would not total the last .7% at Q2 2005.

The correct answer in this example would be 26.3%.

I'd sure appreciate some help with this one.

Many thanks,

Harold

 

[Bob Phillips]

Harold,

Assuming the Quarters in B1:E1, years in A2:A12, test year in I2, test
quarter in I1, then

=SUMPRODUCT((A2:A12<I2)*(B2:E12))+SUMPRODUCT((A2:A12=I2)*(RIGHT(B1:E1,1)<=RI
GHT(I1,1)),B2:E12)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[HGood]

Fantastic!!!

Thanks Bob for that quick and accurate response. That did the trick. I'm a
happy camper.

Harold