# Thread: Variable Range w/in Sumproduct (2002)

1. ## Variable Range w/in Sumproduct (2002)

Within a sumproduct formula, is there any way automate a variable range of an array based on changing start and end dates in other cells?

For instance, see the attachment for an example.

2. ## Re: Variable Range w/in Sumproduct (2002)

Probably a better way.....

=SUMPRODUCT(--(A2:A21>=F6),--(A2:A21<=F7),(B2:B21*C2:C21))/SUMPRODUCT(--(A2:A21>=F6),--(A2:A21<=F7),B2:B21)

3. ## Re: Variable Range w/in Sumproduct (2002)

Or, alternatively:
In E11: =SUMPRODUCT((A2:A21>=F6)*(A2:A21<=F7)*Volume*TAT)
In E12: =SUMPRODUCT((A2:A21>=F6)*(A2:A21<=F7)*Volume)

4. ## Re: Variable Range w/in Sumproduct (2002)

=SUMPRODUCT(--(Date>=F6),--(Date<=F7),Volume,TAT)/SUMPRODUCT(--(Date>=F6),--(Date<=F7),Volume)

Better, if it's the case that the data area is sorted in ascending order on the date column...

G6:

=MATCH(F6,Date)+(LOOKUP(F6,Date)<>F6)

G7:

=MATCH(F7,Date)

The weighted average formula then becomes:

=SUMPRODUCT(INDEX(Volume,G6):INDEX(Volume,G7),INDE X(TAT,G6):INDEX(TAT,G7)/SUM(INDEX(Volume,G6):INDEX(Volume,G7)))

5. ## Re: Variable Range w/in Sumproduct (2002)

Works great, thanks all.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•