I do not want it to be static. I will try your formula, but when it works, I
will be substituting Arrow with Indirect() for the other ranges that I want
to look up.

Thanks and I will try this. I think it will work.

"T. Valko" wrote:

Each range contains a row "FeedAmt".

So, is "FeedAmt" a row header that's included in the named range Arrow?

If so, try this:

=SUMPRODUCT(--(INDEX(Arrow,1,0)=Sheet1!A2),--(INDEX(Arrow,1,0)<=Sheet1!D2),INDEX(Arrow,MATCH("f eedamt",INDEX(Arrow,0,1),0),0))

It looks to me as though you're not really gaining anything from doing it
this way compared to your original formula:

=SUMPRODUCT((Sheet2!D1:AP1=Sheet1!A2)*(Sheet2!D1 :AP1<=Sheet1!D2)*(Sheet2!D28:AP28))

--
Biff
Microsoft Excel MVP


"Duane" wrote in message
...
I understand what the formula is doing. But the range is Arrow. The
actual
range is C26:AP48. there are seveal ranges on the page. Each range
contains
a row "FeedAmt". the dates are across the very top of the worksheet from
D
to AP. So if the dates fall between two dates, I would like to sum up the
values in the "FeedAmt" row of a specific range. the range name will
change,
but all ranges have "FeedAmt in them.

"T. Valko" wrote:

If Arrow refers to Sheet2!D1:AP8 and FeedAmt refers to Sheet2!D28:AP28
then
the equivalent of:

=SUMPRODUCT((Sheet2!D1:AP1=Sheet1!A2)*(Sheet2!D1 :AP1<=Sheet1!D2)*(Sheet2!D28:AP28))

Would be:

=SUMPRODUCT(--(INDEX(Arrow,1,0)=Sheet1!A2),--(INDEX(Arrow,1,0)<=Sheet1!D2),FeedAmt)

--
Biff
Microsoft Excel MVP


"Duane" wrote in message
...
I have a named range. The range consits of about 8 rows, and spans
across
about 30 columns (dates). I want to sum one of the rows in the range
if
it
falls between certain dates. The easy version would be:

=SUMPRODUCT((Sheet2!D1:AP1=Sheet1!A2)*(Sheet2!D1: AP1<=Sheet1!D2)*(Sheet2!D28:AP28))

Where sheet1 A2 is a date, Sheet 1 D2 is a date, and Sheet 2 D28:AP28
is
the
row of numbers I want to sum. the above formula works, but i want the
"Sheet2!D28:AP28" to be referred to as a line in my named range. The
name
of
the range is Arrow, and the line is FeedAmt. That way, I can use the
formula
in several other cases using different range names.

Can this be done?

Thanks