Variable Range w/in Sumproduct (2002)

Topic

New Reply

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.

Reply | Quote

Replies

Probably a better way…..

=SUMPRODUCT(–(A2:A21>=F6),–(A2:A21=F6),–(A2:A21<=F7),B2:B21)

Reply | Quote

Probably a better way…..

=SUMPRODUCT(–(A2:A21>=F6),–(A2:A21=F6),–(A2:A21<=F7),B2:B21)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

=SUMPRODUCT(–(Date>=F6),–(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),INDEX(TAT,G6):INDEX(TAT,G7)/SUM(INDEX(Volume,G6):INDEX(Volume,G7)))

Reply | Quote

Works great, thanks all.

Reply | Quote

Works great, thanks all.

Reply | Quote

=SUMPRODUCT(–(Date>=F6),–(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),INDEX(TAT,G6):INDEX(TAT,G7)/SUM(INDEX(Volume,G6):INDEX(Volume,G7)))

Reply | Quote