Range Sum Question

[dksoreal]

Ok,

I have a open question on summing a range. Here is my situation, jus
need to know if there is a formula that I can apply to make my lif
simple.

I have months going across a row (Jan through Dec) and my categorie
down the columns. This is current month data and I want to have
column off to the right of my data where I can quickly determine th
YTD values. Therefore, if I have data from Jan through Sep, and I wan
to see what the YTD is through August for my data. Is there a way t
have a "key" that I can input into to adjust the sum function to su
just though August?

Any solutions

 

[Paul Corrado]

Lets say that your headings are in row 2, your first row of data is row 3,
and January's data is in column B

=SUM(OFFSET(B3,0,0,1,MATCH("Month"),B2:M2,0)))

Where OFFSET will define a variable width (in this case) range to sum

 

[Domenic]

Assumptions...

B1:M1 contain your dates, in text format (Jan, Feb, etc.)
Column A contains your categories, starting at A2
N1 contains the month of interest or your "key"

Formula...

N2, copied down:

=SUM(B2:INDEX(B2:M2,MATCH($N$1,$B$1:$M$1,0)))

Hope this helps

 

[Frank Kabel]

Hi
lets assume the following:
- row 1 contains the month names as Text values in the format Jan, Feb,
....
- the month name start in B1
- column A contains your categories (starting in A2)
- cell N1 contains your key for selecting the month

Now enter the following formula in N2 (for YTD)
=SUM(OFFSET($B2,0,0,1,MATCH($N$1,$B$1:$M$1,0)))
and copy this down