Conditional sum in a named range

[Duane]

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

 

[T. Valko]

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)

 

[Duane]

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]

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("feedamt",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:

 

[Duane]

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]

Just a heads up...

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

 

[Duane]

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.

 

[Duane]

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.

 

[T. Valko]

OK, good deal!

--
Biff
Microsoft Excel MVP

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.