Sumproduct Offset with an If
 

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!!

Sumproduct Offset with an If
 

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!!

Sumproduct Offset with an If
 

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!!