Conditional Sum

[dksoreal]

Probably a simple solution, but I am struggling.

Here is what I am trying to do. I have months Jan through Dec in a row
with corresponding data below it for a few variables. I want to be
able to input a date and have the formula calculate the total for a
variable up to that date. For example: If I wanted the YTD for March,
I would input "March" in a cell and the formula would know to sum Jan -
Mar in the data table and return the YTD value.

Any suggestions?

 

[Debra Dalgleish]

With "March" in cell A5:

=SUM(INDIRECT("A2:" & ADDRESS(2,MATCH(A5,A1:L1,0))))

 

[Debra Dalgleish]

Or the shorter formula:

=SUM(OFFSET(A2,0,0,1,MATCH(A5,A1:L1,0)))

 

[ryanb.]

I like to use numbers as opposed to typing in the name of the month (i.e.
1=Jan, 2=Feb, 3=Mar, etc). This allows you to use v/hlookup, sumproduct, or
index/match. I use this for the company sales reports where A1 is the month
cell, and the entire report updates to show the current month and YTD v.
prior year... based on the month number in A1 and the year in A2. I also
have one that displays the trailing 12 months sales trend based on the month
in A1. I just find that you are less likely to have an input error using
numbers.

something to consider,

ryanb.