Adding up relevant cells

[James]

Hi all

I have a list of months and % attributed to those months for a number of
countries in a spreadsheet (see below) elsewhere I have a sheet with a list
of countries with various start and end dates.

COUNTRY Jan-08 Feb-08 Mar-08 Apr-08 May-08 Jun-08
Albania 22% 20% 10% 17% 6% 0%
Antarctica 0% 0% 0% 0% 0% 0%
Argentina 4% 4% 2% 3% 6% 6%

What I want to do is for example where I know the country is Argentina, the
start month is Feb-08 and end month is May-08 I want it to add the %s up i.e.
15%.

Thanks in advance

 

[Paul C]

You can establish a range using the Offset function and the sum it. For
simplicity I show the formula on the same sheet.
A B C D E
F G
1/1/2008 2/1/2008 3/1/2008 4/1/2008 5/1/2008 6/1/2008
Albania 22% 20% 10% 17% 6% 0%
Antartica 0% 0% 0% 0% 0% 0%
Argentina 4% 4% 2% 3% 6% 6%

Start End Sum
Argentina 2/1/2008 5/1/2008 15% This is row 7
=SUM(OFFSET(A1,MATCH(A7,A2:A4,0),MATCH(B7,B1:G1,0),1,MATCH(C7,B1:G1,0)-MATCH(B7,B1:G1,0)+1))

A1 is your reference
MATCH(A7,A2:A4,0) establishes how many rows down
MATCH(B7,B1:G1,0) establishes your starting point
1 is your range height (1 row)
MATCH(C7,B1:G1,0)-MATCH(B7,B1:G1,0)+1 establishes your width (4 in this case)
(if you don't want to include the last column get rid of the +1)

One warning - your dates format of Jan-08 could be anywhere from 1/1/08 t0
1/31/08 you will need to check and make sure you enter your start and end
dates the same since the match formula are looking for an exact match.

 

[James]

It works! Thanks Paul


--
James.

You can establish a range using the Offset function and the sum it. For
simplicity I show the formula on the same sheet.
A B C D E
F G
1/1/2008 2/1/2008 3/1/2008 4/1/2008 5/1/2008 6/1/2008
Albania 22% 20% 10% 17% 6% 0%
Antartica 0% 0% 0% 0% 0% 0%
Argentina 4% 4% 2% 3% 6% 6%

Start End Sum
Argentina 2/1/2008 5/1/2008 15% This is row 7

=SUM(OFFSET(A1,MATCH(A7,A2:A4,0),MATCH(B7,B1:G1,0),1,MATCH(C7,B1:G1,0)-MATCH(B7,B1:G1,0)+1))

A1 is your reference
MATCH(A7,A2:A4,0) establishes how many rows down
MATCH(B7,B1:G1,0) establishes your starting point
1 is your range height (1 row)
MATCH(C7,B1:G1,0)-MATCH(B7,B1:G1,0)+1 establishes your width (4 in this case)
(if you don't want to include the last column get rid of the +1)

One warning - your dates format of Jan-08 could be anywhere from 1/1/08 t0
1/31/08 you will need to check and make sure you enter your start and end
dates the same since the match formula are looking for an exact match.