how to conditional sum in a matrix

[HGood]

This is my problem, I have a (Progress Chart) matrix like this, I hope my
tabs format ok after I send it!

A B C D E
1 Q1 Q2 Q3 Q4
2 2005 3.5% 1.7% 2.4% 2.3%
3 2006 2.6% 2.2% 2.8% 2.1%
4 2007 3.1% 2.4% 2.6% 2.7%
5 2008 2.3% 3.1% 2.4% 3.3%
6 2009 3.4% 2.9% 1.4% 3.4%

Each quarter they send a progress chart to me and update the date, e.g. Q2
2006, as two separate entries elsewhere on this chart.

Question - how can I sum the cumulative progress in a given year. Lets say
it is Q3 2006. Based on the entries in in these two cells (Q3) and (2006),
how can I sum Year 2006 for Q1:Q3? It must be able to do this automatically
next quarter too when it is Q4 2006, and after that in Q2 2007.

I tried VLOOKUP, can't get it to work. Arrays don't seem to handle a matrix
like this. Do I need to rearrange it into two columns or can it be done in
this matrix?

I'd sure appreciate someone taking on this challenge and helping with an
answer!

Many thanks,

Harold

 

[Frank Kabel]

Hi
not sure what you want to sum?. If you want the value from
the intersection of year and quarter try
=INDEX(A1:E30,MATCH(2006,A1:A30,0),MATCH("Q3",A1:E1,0))

 

[HGood]

Sorry I wasn't clear. In this case I want the sum of Q1+Q2+Q3 in 2006.
Should equal 7.6% (2.6+2.2+2.8).

Thanks,

Harold

 

[Frank Kabel]

Hi
then try
=SUM(OFFSET($A$1,MATCH(2006,$A$1:$A$10,0)-1,1,1,MATCH("Q3",$A$1:$E$1,1)
-1))

of yourse you can replace 2006 and Q3 with cell references

 

[Aladin Akyurek]

=SUM($B$2:$E$8,MATCH(Year,$A$2:$A$6,0),MATCH(Quarter,$B$1:$E$1,0))

 

[HGood]

Wow! That's cool. I never used OFFSET before. I need to sit down and study
this formula you gave.

Thanks so much Frank,

Harold
=================

 

[Aladin Akyurek]

Make that:

=SUM(INDEX($B$2:$E$8,MATCH(Year,$A$2:$A$6,0),MATCH(Quarter,$B$1:$E$1,0)))