Sumproduct with a variable range

[rhowell100103]

I am stumped on this one. I need to calculate the sumproduct on two columns
from data in a different workbook but the range will vary according to start
and end dates the user will choose.

Example: If I enter 5/22/08 as the start date and 6/2/08 as the end date, I
need a formula to calculate that the arrays for the sumproduct will be G150:
G161 and H150:H161, but if the start date is 5/25/08 with the same end date
the arrays would be G153:G161 and H153:H161.

Can anyone offer any suggestions? Thank you for your time.

Ray

 

[bpeltzer]

You could create your arrays using the longer form of the offset function.
If your start and end dates are in B2 and B3, the first array would be
something like offset($g$1,($b$2+6-date(2007,12,31)),0,$b$3-$b$2+1,1). I'd
be inclined to define names for the first row and number of rows that you
want to include, so that you'd wind up with an array definition like
offset($G$1,first_row-1,0,row_count,1)

 

[rhowell100103]

Thank you. I will give it a try and let you know how I made out.

You could create your arrays using the longer form of the offset function.
If your start and end dates are in B2 and B3, the first array would be
something like offset($g$1,($b$2+6-date(2007,12,31)),0,$b$3-$b$2+1,1). I'd
be inclined to define names for the first row and number of rows that you
want to include, so that you'd wind up with an array definition like
offset($G$1,first_row-1,0,row_count,1)

I am stumped on this one. I need to calculate the sumproduct on two columns
from data in a different workbook but the range will vary according to start

[quoted text clipped - 8 lines]

Ray

 

[rhowell100103]

bpeltzer,

That worked beautifully. The answer is so simple yet it eluded me. I would
never have thought of incorporating the height and width options of OFFSET as
I don't think I have ever had cause to use them before. Thank you so much
for taking your time to help me. I greatly appreciate it.

You could create your arrays using the longer form of the offset function.
If your start and end dates are in B2 and B3, the first array would be
something like offset($g$1,($b$2+6-date(2007,12,31)),0,$b$3-$b$2+1,1). I'd
be inclined to define names for the first row and number of rows that you
want to include, so that you'd wind up with an array definition like
offset($G$1,first_row-1,0,row_count,1)

I am stumped on this one. I need to calculate the sumproduct on two columns
from data in a different workbook but the range will vary according to start

[quoted text clipped - 8 lines]

Ray