Hi everyone. Is there a way to have a sumproduct embedded with an
offset function. Essentially, I will write the formula in Column C.
I need to scan column D, and for ever instance of the word "Sales"
multiply the cell directly to the right in column E by the cell in
column A, BUT 2 rows above! Possible? Thanks!!

You should be able to just offset your range without using the OFFSET
function.
Example:

=SUMPRODUCT(--(D3:D13="Sales"),(C1:C11)*(E3:E13))

HTH,
Paul

--

"Steve" wrote in message
...
Hi everyone. Is there a way to have a sumproduct embedded with an
offset function. Essentially, I will write the formula in Column C.
I need to scan column D, and for ever instance of the word "Sales"
multiply the cell directly to the right in column E by the cell in
column A, BUT 2 rows above! Possible? Thanks!!

Steve

It seems like you don't really need an offset function, you just need
your arrays in the sumproduct to be offset. A formula like

=SUMPRODUCT(--(D3:D12="sales"),E3:E12,A1:A10)

will check for the word "sales" in column D, and where it finds it, it
will multiply the cell in that row in column E by the value in column
A two rows up. Usually when one sees a formula like that, something
is screwed up. In your case, it may do just what you want.

Good luck.

Ken
Norfolk, Va


On Mar 28, 4:16*pm, Steve wrote:
Hi everyone. *Is there a way to have a sumproduct embedded with an
offset function. *Essentially, I will write the formula in Column C.
I need to scan column D, and for ever instance of the word "Sales"
multiply the cell directly to the right in column E by the cell in
column A, BUT 2 rows above! *Possible? *Thanks!!