Adding up relevant cells

  • Thread starter Thread starter James
  • Start date Start date
J

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
 
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.
 
It works! Thanks Paul


--
James.


Paul C said:
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.
Click to expand...
 
Back
Top