Sumproduct Offset with an If

[Steve]

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

 

[PCLIVE]

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

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

HTH,
Paul

 

[krcowen]

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(--(D312="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