Offset match formula

[lj]

Hi,
I have the following offset match formula that I use to correctly pull
data into a summary sheet by date and metric name. Every day I update
my week and it pulls in the new data corrrectly. Is it possible to
add to this formula so that I can sum range of dates in my source
sheet? For example, I have a YTD total that I would like to be able
to use this formula for. But in order to do that I need to adjust it
so that it will sum up all of the rows between two dates. Is this
possible? What would the formula look like? Do I need to use a
different formula?

=OFFSET(Source!$F$118:$ES$2010,MATCH($AR$26,Source!$G$119:$G
$2010,0),MATCH(A$28,Source!$G$14:$ES$14,0))

Here's an example of my data

Units Cash
1/1/2009 2 4
1/2/2009 5 10
1/3/2009 3 5
1/4/2009 1 3
1/5/2009 4 6

 

[Per Jessen]

Hi

With your data in columns A:C, StartDate in F2 and EndDate in G2, this
formula will calculate YTD (on column C) between StartDate and
EndDate:

=SUMPRODUCT(--(A2:A1000>=F2),--(A2:A1000<=G2),C2:C1000)

Hopes this helps

 

[lisaj]

Hi

With your data in columns A:C, StartDate in F2 and EndDate in G2, this
formula will calculate YTD (on column C) between StartDate and
EndDate:

=SUMPRODUCT(--(A2:A1000>=F2),--(A2:A1000<=G2),C2:C1000)

Hopes this helps

---
Per




- Show quoted text -

Thanks, this does help. I was using an offset match formula before to
find the correct data column from a source tab. Is it possible to
incorporate this formula into the one you gave me so that I can be
sure I'm looking at the correct column?

 

[lisaandalec]

Thanks, this does help.  I was using an offset match formula before to
find the correct data column from a source tab.  Is it possible to
incorporate this formula into the one you gave me so that I can be
sure I'm looking at the correct column?- Hide quoted text -

- Show quoted text -

So what I'm trying to do is to find an offset match formula that
incorporates an array and allows me to adjust my date range each week
so I can sum all of the data points that I need. I want the formula
to match on cash and sum cash on dates between 1/1/2009 and 1/7/2009.
I used a formula like this a long time ago and I can't remember how it
worked. Please help.

1/1/2009, 1/7/2009
CASH = ????


Date Units Cash
1/1/2009      2 4
1/2/2009      5 10
1/3/2009      3 5
1/4/2009      1 3
1/5/2009      4 6
1/6/2009 3 4
1/7/2009 4 9