Conditional sum in a named range

D

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

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)
 
D

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

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:
 
D

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

T. Valko

Just a heads up...

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

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.
 
D

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

T. Valko

OK, good deal!

--
Biff
Microsoft Excel MVP


Duane said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top