Conditional sum in a named range
 

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

Conditional sum in a named range
 

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

Conditional sum in a named range
 

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

Conditional sum in a named range
 

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

Conditional sum in a named range
 

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

Conditional sum in a named range
 

Just a heads up...

If Arrow is a *dynamic* range defined using functions like OFFSET then
INDIRECT won't work.

--
Biff
Microsoft Excel MVP


"Duane" wrote in message
...
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

Conditional sum in a named range
 

I know it is difficult to explain on these boards exactly what we try to do.
I appreciate the help and in the end this is the formula that does what I
want.

=SUM(INDEX(Arrow,3,A1):INDEX(Arrow,3,D1))

Far from what I was trying. It was a fluke really. A1 and D1 are dates
(just the day of the month so a single number. In this case 1 and 5 (Sept 1
and Sept 5) This will sum the numbers between the two dates! I can easily
change the range to be using an Indirect.

Thanks for you time.

"T. Valko" wrote:

Just a heads up...

If Arrow is a *dynamic* range defined using functions like OFFSET then
INDIRECT won't work.

--
Biff
Microsoft Excel MVP


"Duane" wrote in message
...
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

Conditional sum in a named range
 

I should have mentioned....Feedamt happens to be the 3rd line in any of the
arrays in this case. That is why the 3 in the index.

"Duane" wrote:

I know it is difficult to explain on these boards exactly what we try to do.
I appreciate the help and in the end this is the formula that does what I
want.

=SUM(INDEX(Arrow,3,A1):INDEX(Arrow,3,D1))

Far from what I was trying. It was a fluke really. A1 and D1 are dates
(just the day of the month so a single number. In this case 1 and 5 (Sept 1
and Sept 5) This will sum the numbers between the two dates! I can easily
change the range to be using an Indirect.

Thanks for you time.

"T. Valko" wrote:

Just a heads up...

If Arrow is a *dynamic* range defined using functions like OFFSET then
INDIRECT won't work.

--
Biff
Microsoft Excel MVP


"Duane" wrote in message
...
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

Conditional sum in a named range
 

OK, good deal!

--
Biff
Microsoft Excel MVP


"Duane" wrote in message
...
I should have mentioned....Feedamt happens to be the 3rd line in any of the
arrays in this case. That is why the 3 in the index.

"Duane" wrote:

I know it is difficult to explain on these boards exactly what we try to
do.
I appreciate the help and in the end this is the formula that does what I
want.

=SUM(INDEX(Arrow,3,A1):INDEX(Arrow,3,D1))

Far from what I was trying. It was a fluke really. A1 and D1 are dates
(just the day of the month so a single number. In this case 1 and 5
(Sept 1
and Sept 5) This will sum the numbers between the two dates! I can
easily
change the range to be using an Indirect.

Thanks for you time.

"T. Valko" wrote:

Just a heads up...

If Arrow is a *dynamic* range defined using functions like OFFSET then
INDIRECT won't work.

--
Biff
Microsoft Excel MVP


"Duane" wrote in message
...
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